Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Dealing with a long string

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...