Choosing the right field data type in SQL Server 2008 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 &lt;= 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 &lt;= 8000, but the other 10% will be &lt;= 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.