I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters that are retrieved from a terminal screen.

There will be about 50 million rows at first, and initially, we estimated the field will required <= 7,500 characters, so I opted to use the VARCHAR(8000) datatype. After more analysis, we determined that probably about 90% of the 30 million rows will be <= 8000, but the other 10% will be <= 15,000.

I obviously can't use the VARCHAR due to its 8,000 limit, so I assumed my only option is the text datatype. I'm concerned the overhead of 'text' and that it may be removed eventually. I'm now looking at VARCHAR(max), but I've never used it in a production environment.

Should I use varchar(max), text, or look for a way to compress that 10% of data and store it in a varchar(8000)?


You should use varchar(max). The text data type "will be removed in a future version of Microsoft SQL Server."

Related on SO Using varchar(MAX) vs TEXT on SQL Server

The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.