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

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.

Answer 2

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