August 11, 2015 at 9:00 am
I was bit by this developing one time. I was genericizing a stored procedure to work with newer tables as they were created (the procedure was for cleaning up old archive data), so I had a very large dynamic sql query. A column was added to an already long table, pushing the dynamic query outside its variable size. When I tried to print to see how much I was off by, I only got 4000 characters. It wasn't until someone told me that PRINT wouldn't give me everything did I realize that, even though I was expanding the variable size and the query will work, the PRINT would continue to mislead me.
August 11, 2015 at 2:38 pm
Interesting question - but I am hoping there will be a final resolution in the comments about a possible error in the explanation?
Thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 11, 2015 at 2:50 pm
Toreador (8/11/2015)
"however, you have to append in increments with each increment less than or equal to 4000"Not true.
declare @test-2 nvarchar(max) = ''select @test-2 = @test-2 + N'x' + cast(space(6000) as nvarchar(max)) + N'x'
select len(@test)
select @test-2 = @test-2 + N'x' + cast(space(6000) as nvarchar(max)) + N'x'
select len(@test)
returns 6002 and 12004
I agree. Thanks for pointing this.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 11, 2015 at 2:54 pm
briankwartler (8/11/2015)
And for those of you who are wondering why LEN(@SQL) is 48,893 it's because
LEN(', CONVERT(VARCHAR(10), CAST(1 AS INT )) as ABC')is 47 and there are 9 single-digit numbers (1 through 9)
LEN(', CONVERT(VARCHAR(10), CAST(10 AS INT )) as ABC')is 48 and there are 90 two-digit numbers (10 through 99)
LEN(', CONVERT(VARCHAR(10), CAST(100 AS INT )) as ABC')is 49 and there are 900 three-digit numbers (100 through 999)
LEN(', CONVERT(VARCHAR(10), CAST(1000 AS INT )) as ABC')is 50 and there is 1 four-digit number (1000)47 * 9 = 423
48 * 90 = 4320
49 * 900 = 44100
50 * 1 = 50
and 423 + 4320 + 44100 + 50 = 48893
Superb explanation. Thanks Brian
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 12, 2015 at 12:04 am
SQLRNNR (8/11/2015)
Yes the string length in the messages tab is 4000 characters. And the variable length is 48,893. Are you seeing something different in the messages tab?
Damn 😎 didn`t see the last line of code.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
August 12, 2015 at 9:44 am
Hany Helmy (8/12/2015)
SQLRNNR (8/11/2015)
Yes the string length in the messages tab is 4000 characters. And the variable length is 48,893. Are you seeing something different in the messages tab?
Damn 😎 didn`t see the last line of code.
It was easy to miss.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 10:07 am
Thats the size limitation. Good to remember the basics.
Thanks.
August 13, 2015 at 4:37 am
Mighty (8/11/2015)
Toreador (8/11/2015)
"however, you have to append in increments with each increment less than or equal to 4000"Not true.
+1
Not sure where that assumption is coming from. Seems incorrect to me too.
This incorrect assumption comes from a very comon mistake made when people write simple test code, e.g.
SET @TestVar = @TestVar + REPLICATE('x', 10000);
The 'x' is interpreted as varchar, not varchar(max), so the end result of the REPLICATE is a varchar(8000) value that will then be appended to @TestVar
You will not have this problem if you change the code to
SET @TestVar = @TestVar + REPLICATE(CAST('x' AS varchar(MAX)), 10000);
On the question - I seem to remember that the maximum string length displayed in SSMS is actually a configurable setting.
August 14, 2015 at 6:56 am
Hugo Kornelis (8/13/2015)
Mighty (8/11/2015)
Toreador (8/11/2015)
"however, you have to append in increments with each increment less than or equal to 4000"Not true.
+1
Not sure where that assumption is coming from. Seems incorrect to me too.
This incorrect assumption comes from a very comon mistake made when people write simple test code, e.g.
SET @TestVar = @TestVar + REPLICATE('x', 10000);The 'x' is interpreted as varchar, not varchar(max), so the end result of the REPLICATE is a varchar(8000) value that will then be appended to @TestVar
You will not have this problem if you change the code to
SET @TestVar = @TestVar + REPLICATE(CAST('x' AS varchar(MAX)), 10000);On the question - I seem to remember that the maximum string length displayed in SSMS is actually a configurable setting.
In SSMS you can configure the maximum length displayed in result sets (up to 65,535 characters for grid view or 8,192 for text view), but not from PRINT -- that's restricted by SQL Server: "A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string."
Results to Grid options: https://msdn.microsoft.com/en-us/library/ms187068.aspx
(The help page for Results to Text does not specify a limit, but any value > 8,192 is replaced with 8,192.)
PRINT: https://msdn.microsoft.com/en-us/library/ms176047.aspx
August 18, 2015 at 7:43 am
Good question, nice tidbit, thanks.
August 28, 2015 at 6:32 am
SO why is 48893 wrong?
August 28, 2015 at 8:45 am
The correct answer is:
Variable @sql accepts 48,893 as string length but Print @sql does not display more than 4000 characters
See https://msdn.microsoft.com/en-us/library/ms176047.aspx and in particular, under Remarks, where the article states the following:
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).
- Brian
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply