Normally, if I’ve ever needed a large amount of text data stored in a database field, I would use a column type of text. I would usually do this when going beyond 256 characters. I think this stems back to use MS SQL 6.

Now as of SQL 7, the varchar type can hold around 8000 characters (8k) and nvarchar can hold around 4000 characters. This limitation stems from the 8KB internal page size SQL Server uses to save data to disk. But who really wants to deal with putting in client-side validation of 8k characters when you can just make it unlimited?

But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.

Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file).

Most clients that I’ve worked on are still on SQL 2000, and maybe 2005 – so I haven’t really caught up to the nifty 2005 and 2008 changes yet. Reason? MS SQL Servers are freak’n expensive!

In SQL Server 2005 Microsoft added support for the varchar(max) and nvarchar(max) data types. The difference between varchar(8000) and varchar(max) is that it can now store up to 2GB of data (about 2,147,483,647 characters) instead of 8K. That seems to be plenty in my books for anything I need it for.

The other difference is that varchar(max) is stored in the same type of data pages used for other data types, instead of a blob file, using as much as it can in the row (8k) then creates additional pages for the leftover text. So a lot of text stored will actually fit inside the row.

The LIKE statement is identical between TEXT and MAX. But now you can use the GROUP BY statement. But watch your performance when using these. It is usually better to use Full Text Indexing and CONTAINS instead of the LIKE statement. LIKE is simpler to implement and can be suitable for small amounts of data, but has poor performance with large data since it is not indexable.

But check this out: You can now use the = operator on MAX data types. So you can now do this:

SELECT * FROM Table1
WHERE MaxTypeCol = 'value1'

Microsoft is recommending using MAX data types instead of BLOBs now as BLOBs will be deprecated in a future release.

Now this doesn’t mean you can define a varchar over 8000 bytes such as varchar(10000). Anything over 8000 will need the MAX keyword.

Another thing to consider is don’t go creating MAX types for columns that store your name or addresses. The MAX types does still add overhead that may cause performance issues along with poor design practices. The MAX type will also remove the ability for online-reindexing.