if you’re using SQL Server 2005 and above use varchar(MAX). Text datatype is deprecated and should not be used for new development work.
In SQL server 2005 new datatypes were introduced:
nvarchar(max) They have the advantages of the old text type: they can contain op to 2GB of data, but they also have most of the advantages of
nvarchar. Among these advantages are the ability to use string manipulation functions such as substring().
Also, varchar(max) is stored in the table’s (disk/memory) space while the size is below 8Kb. Only when you place more data in the field, it’s is stored out of the table’s space. Data stored in the table’s space is (usually) retrieved quicker.
In short, never use Text, as there is a better alternative: (n)varchar(max). And only use varchar(max) when a regular varchar is not big enough, ie if you expect teh string that you’re going to store will exceed 8000 characters.
As was noted, you can use SUBSTRING on the TEXT datatype,but only as long the TEXT fields contains less than 8000 characters.
Plus By “varchar” and “nvarchar” are better for searching; as in issuing any queries which result depends on the value of the
VARCHAR column. This includes using it in any