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

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(), SUBST开发者_运维技巧RING(), 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)?

• 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.

Crystal Reports 12 (and other versions, as far as I know) doesn't handle varchar(max) properly and interprets it as varchar(255) which leads to truncated data in reports.

So if you're using Crystal Reports, thats a disadvantage to varchar(max). Or a disadvantage to using Crystal, to be precise.

See:
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=5843&PID=17503
http://michaeltbeeitprof.blogspot.com/2010/05/crystal-xi-and-varcharmax-aka-memo.html

If you genuinely don't need indexes and it is a large column you should be fine. Varchar (max) appears to be exactly what you need and you will have less problems with existing code than you would if you used text.

Make sure to test any updates where text is added to the existing text. It should work using regular concatenation, but I'd want to be able to prove it.