Print a nvarchar variable

Question

Print a nvarchar variable

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

webrunner SSC-Dedicated Points: 31984 More actions · Answer 1

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

Naidu PK SSCrazy Points: 2481 More actions · Answer 2

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

Naidu PK SSCrazy Points: 2481 More actions · Answer 3

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions Follow · Answer 6

Thats the size limitation. Good to remember the basics.

Thanks.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sknox SSChampion Points: 12388 More actions · Answer 8

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

August 18, 2015 at 7:43 am

#1820729

Good question, nice tidbit, thanks.

farmerme SSC Veteran Points: 282 More actions · Answer 10

farmerme

SSC Veteran

Points: 282

August 28, 2015 at 6:32 am

#1823023

SO why is 48893 wrong?

briankwartler Ten Centuries Points: 1332 More actions · Answer 11

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

Print a nvarchar variable

Cookies on SQLServerCentral