Take your VARCHAR to the MAX

 One of the ‘downsides’ of dynamically creating SQL statements is that you can never tell on beforehand how
big your statement will eventually become. This is especially annoying if you have to stay within a maximum number of characters.
Fortunately, since the introduction of SQL Server 2005 we have another toy to play with, namely the VARCHAR(MAX) datatype.
According to its definition, VARCHAR(MAX) should be able to hold up to a whopping 2GB of data, which makes it an excellent choice in case we can’t predict the lenght for our (temporary) variable.

But working with the VARCHAR(MAX) datatype can be a bit tricky.
You wouldn’t be the first to see your dynamic string getting truncated at 8000 characters (or 4000 if you’re using nvarchar), even though you explicitly defined a VARCHAR(MAX).
The good news is, you *can* hold 8000+ characters in a VARCHAR(MAX). How? Let’s take a look under the hood!

Working with a huge string wouldn’t be very readable, so let’s take the following shortcut:

   1: DECLARE @l_HugeString AS VARCHAR(MAX)

   2: SET @l_HugeString = REPLICATE ('X', 5000) + REPLICATE ('X', 5000)

At first glance it looks like our huge string will now hold 10.000 X’s. But if we check the current lenght, we get the following result:

   1: SELECT LEN(@l_HugeString)

   2:  

   3: Result: 8.000

So where did our 2000 missing characters go?
This is because the righthand side of the expression will be evaluated (concatenated) before the result gets assigned to the @l_HugeString variable.
The result of concatenating varchar(5000) + varchar(5000) will also become a varchar, and varchar can only hold up to 8000 characters.
So the result is getting truncated from 10.000 -> 8000 before it’s assigned to the VARCHAR(MAX). variable.

In order to get around this behaviour, we have to make sure that the expression on the righthand side is of the VARCHAR(MAX). type as well.
One way to achieve this, is by adding a VARCHAR(MAX). in the mix:

   1: DECLARE @l_HugeString AS VARCHAR(MAX)

   2: SET  @l_HugeString = CAST('' AS VARCHAR(MAX)) + REPLICATE ('X', 5000) 

   3:                         + REPLICATE ('X', 5000)

   4: SELECT LEN(@l_HugeString)

   5:  

   6: Result: 10.000

Or a more elegant solution:

   1: DECLARE @l_HugeString AS VARCHAR(MAX) 

   2: SET  @l_HugeString = '' 

   3: SET  @l_HugeString = @l_HugeString + REPLICATE ('X', 5000) + REPLICATE ('X', 5000) 

   4: SELECT LEN(@l_HugeString)

   5:  

   6: Result: 10.000

Happy concatenating!