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