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!

3 thoughts on “Take your VARCHAR to the MAX

  1. – Good point. It’s good to realise that it’s an issue with SQL dataypes in general, not only varchar(max), as the following example shows:

    DECLARE @NUMBER FLOAT
    SET @NUMBER = 3 / 2
    SELECT @NUMBER
    GO
    — Result : 1

    DECLARE @NUMBER FLOAT = 1
    SET @NUMBER = @NUMBER * 3 / 2
    SELECT @NUMBER
    GO

    — Result: 1.5

    — Another way to solve the issue, it to make sure all variables are of the same type:

    DECLARE @L_HUGESTRING AS VARCHAR(MAX)
    DECLARE @X VARCHAR(MAX) = ‘X’
    SET @L_HUGESTRING = REPLICATE (@X, 5000) + REPLICATE (@X, 5000)
    SELECT LEN(@L_HUGESTRING)
    GO

  2. Thanks a lot for explaining how to fight the truncation of varchar(max) when concatenating! Very helpful article!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>