Any hidden pitfalls changing a column from varchar(8000) to varchar(max)?

Question

I have a lot (over a thousand places) of legacy T-SQL code that only makes INSERTs into a varchar(8000) column in a utility table. Our needs have changed and now that column needs to be able to handle larger values. As a result I need to make that column varchar(max). This is just a plain data column where there are no searches preformed on it, no index on it, only one procedure reads it, it is INSERT and forget for the application (almost like a log entry).

I plan on making changes in only a few places that will actually generate the larger data, and in the single stored procedure that processes this column.

• Are there any hidden pitfalls changing a column from varchar(8000) to a varchar(max)?
• Will all the T-SQL string functions work the same, LEN(), RTRIM(), SUBSTRING(), etc.
• Can anyone imagine any reason why I'd have to make any changes to the code that thinks the column is still varchar(8000)?

Answer 1

• All MAX types have a small performance penalty, see Performance comparison of varchar(max) vs. varchar(N).
• If your maintenance include online operations (online index rebuild), you will lose the possibility to do them. Online operations are not supported for tables with BLOB columns:

  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
  • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.

The performance penalty is really small, so I wouldn't worry about it. The loss of ability to do online rebuilds may be problematic for really hot must-be-online operations tables. Unless online operations are a must, I'd vote to go for it and change it to MAX.