Every now and again you have to put a really long string (more than 8000 characters). Dynamic SQL is the most frequent example where I see this but I do see it elsewhere as well and it’s very easy to make a simple mistake. This is caused by the fact that a string is a varchar, at least based on all of the evidence I can find. It would probably take a real internals expert to say for sure.

-- This is the best evidence I could find of the 
-- data type of a string
SELECT SQL_VARIANT_PROPERTY('A string','BaseType');
--Returns: varchar

Note that it is varchar and not varchar(max). Varchar and varchar(max) have very different size limits. A varchar(max) has a limit of 2GB and a varchar has a limit of 8000 characters. So what is this mistake I’m talking about? Watch.

DECLARE @str varchar(max);
SET @str = REPLICATE('1',950) +
REPLICATE('2',950) +
REPLICATE('3',950) +
REPLICATE('4',950) +
REPLICATE('5',950) +
REPLICATE('6',950) +
REPLICATE('7',950) +
REPLICATE('8',950) +
REPLICATE('9',950) +
REPLICATE('0',950); 
SELECT LEN(@str);
GO
-- Output 8000

And of course 10 * 950 characters is 9500. This is a rather contrived example but again if you are dealing with long pieces of dynamic SQL it can and does come up occasionally. So what’s the fix? Add smaller strings multiple times like this.

DECLARE @str varchar(max);
SET @str = REPLICATE('1',950) +
REPLICATE('2',950) +
REPLICATE('3',950) +
REPLICATE('4',950) +
REPLICATE('5',950); 
SET @str = @str + 
REPLICATE('6',950) +
REPLICATE('7',950) +
REPLICATE('8',950) +
REPLICATE('9',950) +
REPLICATE('0',950); 
SELECT LEN(@str);
-- Output 9500

Personally I try to break up strings long before I run into issues, it’s safer that way. I still mess up occasionally though, and when I get a weird error that looks like my string has been truncated this is one of the first things I check for.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, strings, T-SQL