This may seem a logical solution to a simple problem, but I have seen a few people bitten by it.
Why does the following TSQL return LEN(@s3) as 8000 instead of 16000?
DECLARE @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(max)
SELECT @s1 = REPLICATE('A', 8000), @s2 = REPLICATE('B', 8000)
SELECT @s3 = @s1 + @s2
SELECT LEN(@s3)
The problem is that if you concatenate two varchar(8000) variables, you will be left with a varchar(8000) variable. This is the same issue as populating a float with the division of two integers. You will lose precision unless cast before the division
E.g.
DECLARE @i1 int, @i2 int, @f1 float
SELECT @i1 = 1, @i2 = 3
SELECT @f1 = @i1 / @i2
SELECT @f1
Returns 0, and
DECLARE @i1 int, @i2 int, @f1 float
SELECT @i1 = 1, @i2 = 3
SELECT @f1 = CAST(@i1 AS float) / CAST(@i2 AS float)
SELECT @f1
Returns 0.333333333333333
So,
DECLARE @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(max)
SELECT @s1 = REPLICATE('A', 8000), @s2 = REPLICATE('B', 8000)
SELECT @s3 = CAST(@s1 AS varchar(max)) + CAST(@s2 AS varchar(max))
SELECT LEN(@s3)
Will work.
However, why then does the following also work without the cast?
DECLARE @s1 varchar(10), @s2 varchar(10), @s3 varchar(20)
SELECT @s1 = REPLICATE('A', 10), @s2 = REPLICATE('B', 10)
SELECT @s3 = @s1 + @s2
SELECT LEN(@s3)
The reason is that varchar(n) variables are physically stored in the same way - irrespective of length. Whereas an varchar(MAX) is effectively a text under the covers and needs to worry about managing pointers to the relevant data page (although this is abstracted away from the developer).