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 nvarchar(max) = ''
select @test = @test + N'x' + cast(space(6000) as nvarchar(max)) + N'x'
select len(@test)
select @test = @test + 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 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy