21
What’s the difference of using text instead of type varchar to store information in the database?
Is there a performance problem? Because I have a table in the database that has a column text and she gives several times error of timeout.
3 answers
22
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 great.
VARCHAR can have a size limit and is stored directly on the data line (unless you exceed a limit, I think 8KB). VARCHAR(MAX) is essentially the same as TEXT
Essentially there are no major performance problems in most situations. There may be differences if you are in blob since it is an indirect. But it can also help other things. But this is not the question.
The current recommendation is to use the VARCHAR. TEXT can even be removed in future versions, according to Microsoft. You should convert this column because of this.
-
I was reading about it and I came across information that in my view is important, the question of the index, I do not know in Sqlserver as it is but in the case of Mysql says that you can not put index in text field but in varchar(x) yes... it would be interesting to talk about it, I was curious about the Sqlserver case
-
@Marcelo is interesting I will leave the daughter-in-law for his same comment, even because this is a guy that should not be used anyway.
8
Whereas VARCHAR(MAX) is almost the same thing 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 limit of 8k 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 comparators = and GROUP BY. But if you have a lot of data on these VARCHAR can have serious performance problems.
If you use Like, Full Text Index and CONTAINS they behave the same.
If you are going to query these fields and they have a lot of data, the recommended is the Full Text Index.
Source: This response from Soen of Robin Day
6
Text:
Non-unicode data of variable length on the server code page and with a maximum character 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 character string, the storage size may be less than 2,147,483,647 bytes.
Varchar:
Non-unicode character string data of variable length. n defines the length of the character 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. Synonyms for ISO varchar are char Varying or Character Varying.
Source: http://msdn.microsoft.com/pt-br/library/ms187752.aspx
Browser other questions tagged database sql-server typing varchar
You are not signed in. Login or sign up in order to post.
Your problem certainly isn’t in the data type. You did a test turning the column into
VARCHARand see what happens?– Maniero