Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
Thank you for you input and link. I believe the varchar(max) will work perfectly. –  Eddie Paz Apr 4 '12 at 20:27
    
Sounds like varchar(max) is the way to go here, but remember there are limitations on indexing and searching these columns. –  JHFB Apr 12 '12 at 12:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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