varchar(MAX) truncating

I was reading Karen Delaney's excellent Inside SQL Server 2005 book about the SQL Server 2005 storage engine, and she explained quite a neat feature: varchar(MAX).

varchar(MAX) is a funny sort of varchar, only you aren't restricted to only 8000 bytes (or characters - to varchar it's all the same) but in fact the maximum length of the column is the maximum length of any LOB type (2^31). Basically, if a column is set to varchar(MAX) and you have less than 8000 bytes then SQL Server internally stores the column as a varchar column, and if you exceed 8000 bytes then it stores it as LOB data.

The interesting thing about varchar(max) is that it appears that unlike TEXT columns you can actually apply normal string manipulating SQL on it (i.e. replicate, left, right, etc). Yes, that's right, no more mucking about with TEXTPTR manipulation!

The trap to watch out for is that if you decide to do something like join a varchar(MAX) value to a varchar value in order to create a new varchar(MAX) value, if the new value becomes greater than 8000 characters then the new value will be truncated to 8000 bytes.

e.g.

declare @theText varchar(MAX)
set @theText = replicate('x', 7095) + 'the end bit'
select len(@theText)

(shamelessly stolen from this blog)

While you might think that the length of the string will show as 8006, the length is actually 8000.

Why does this occur?

The answer is actually found in SQL Server books online, under the section Precision, Scale and Length. This says that:

When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.

When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.

Therefore, be warned! unless you cast the string to a varchar(MAX), then you might not get the results you expect!

For the above example, incidently, you would do the following:

declare @theText varchar(MAX)
set @theText = replicate(cast 'x' as varchar(MAX)), 7095) + cast('the end bit' as varchar(MAX))
select len(@theText)