Difference between text type and varchar type in SQL Server
What is the difference between using the text type instead of the varchar type to store information in the database?
Is there a performance problem? Because I have a table in the bank that has a column text and it several times error timeout.
3 answers
TEXT it does not have a specific size limit beyond the database maximum. It is stored in the specific area for blobs since the expectation is that it will be large.
VARCHAR it can have a size limit and is stored directly on the data line (unless it exceeds a limit, I think 8KB). VARCHAR(MAX) is essentially the same as TEXT
There are essentially no major performance issues in most situations. There may be differences if 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 VARCHAR. TEXT 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 in the rows, unless it exceeds the 8K limit and then it will be stored as a blob.
The use of LIKE is identical with both types. An additional feature that VARCHAR allows is that you can use it in comparators = and GROUP by . But if you have a lot of data in these VARCHAR can have serious performance problems.
If you use Like, Full Text Index and CONTAINS they behave the same way.
If you are querying these fields and they have a large amount of data, the recommended is Full Text Index.
Source: this resposa do Soen do Robin Day
Text:
Non-Unicode data of variable length on the server 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 storage 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 entered data + 2 bytes. ISO synonyms for varchar are char varying or character varying.
Source: http://msdn.microsoft.com/pt-br/library/ms187752.aspx