Difference between type text and varchar type in SQL Server

TEXT does not have a specific size limit beyond the maximum of the database. It is stored in the specific area for blobs since the expectation is that it will be large.

% can_ have a size limit and is stored directly in the data line (unless it exceeds a limit, I think 8KB). VARCHAR is essentially the same as VARCHAR(MAX)

Essentially there are no major performance issues in most situations. There may be differences if you are in the blob since it is an indirection. But it can also help other things. But this is not the point.

The current recommendation is to use TEXT . VARCHAR can even be removed in future versions, according to Microsoft . You should convert this column because of this.

Whereas VARCHAR (MAX) is almost the same as TEXT. The basic difference is that the TEXT type will always be stored in blob storage areas, and varchar will always try to store the data directly on the lines, except if it exceeds the 8k limit and then it will be stored as blob.

The use of LIKE is identical with both types. An additional feature that VARCHAR allows is that you can use it in = and GROUP BY comparators. But if you have too much data in these VARCHARs you can have serious performance issues.

If you use Like, Full Text Index and CONTAINS they behave the same way.

If you are querying these fields and they have a lot of data, the Full Text Index is recommended.

Source: This SOEn Resolve from Robin Day

Text:

Variable length non-Unicode data on the server's code page and with a maximum string length of 2 ^ 31-1 (2,147,483,647). When the server code page uses two-byte characters, the store will still be 2,147,483,647 bytes. Depending on the string, the storage size may be less than 2,147,483,647 bytes.

Varchar:

Non-Unicode string data of variable length. n defines the length of the string and can be a value from 1 to 8,000. max indicates that the maximum storage size is 2 ^ 31-1 bytes (2 GB). The storage size is the actual length of the inserted data + 2 bytes. The ISO synonyms for varchar are char varying or character varying.

Source: link