Are there disadvantages to using VARCHAR(MAX) in a table? Are there disadvantages to using VARCHAR(MAX) in a table? sql-server sql-server

Are there disadvantages to using VARCHAR(MAX) in a table?


Sounds to me like you plan to use the varchar(MAX) data type for its intended purpose.

When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.

For further reading, check out Books Online: char and varchar


You cannot create indexes on varchar(max) ( and nvarchar(max)) columns (although they can be included in them. But who would include a column in an index that could get to 2GB?!) so if you want to search on this value, you will do a scan each time unless you use full-text indexes. Also, remember that any report designer or presentation designer (web or otherwise) must assume that someone might put the Encyclopedia into that column and design around it. Nothing is worse than hearing "the users probably won't do X". If a user can do it, they will do it. If a user can put in a tome into a column, at some point they will. If they never should, then IMO, it makes more sense to cap the column size at some reasonable level and if a user tries to stuff more into that column that is allowed, it would elicit a discussion of whether they should be entering that value into that column in the first place.


I've seen some problems - particularly with scalar functions (but these are generally horrible, anyway) which return varchar(MAX) and then aren't re-cast. For instance, say you have a special function CleanString(somevarcharmax) returns varchar(max) and call it on varchar(50) but don't CAST(CleanString(varchar10col) AS varchar(10)) - nasty performance issues.

But typically, when you have varchar(max) columns in a table, you shouldn't be performing those kinds of operations en masse, so I'd say if you are using it properly for your data needs in the table, then it's fine.