Nvarchar Max no of character size

Question

Nvarchar Max no of character size

Re1

SSC Eights!

Points: 991
More actions
December 29, 2015 at 11:24 am

#314485

I am trying to Select data from a table for nvarchar max datatype column.
it gives me 43769 charcters only. When I check the column lengh it gives me 238894 length.
CREATE TABLE Test_NtextData
(
Data NVARCHAR(MAX)
)
GO
DECLARE @Ntext NVARCHAR(MAX)=''
DECLARE @I INT=1
WHILE (@I<=50000)
BEGIN
SET @Ntext=@Ntext+CONVERT(NVARCHAR(100),@I)
SET @I=@I+1
END
INSERT INTO Test_NtextData
SELECT @Ntext
SELECT LEN(Data) from Test_NtextData ---238894 character length
SELECT Data from Test_NtextData ---it gives 43679 Charters only
My concern is why i am getting 43679 characters in select insted of 238894 charcter. and if i wanted to use that entire column data for further processing then how can i use it.

Viewing 5 posts - 1 through 4 (of 4 total)

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

drew.allen SSC Guru Points: 76996 More actions · Answer 1

Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:

SELECT Data from Test_NtextData

FOR XML PATH('')

This will output the data as XML. SSMS allows XML text up to 2GB.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

drew.allen (12/29/2015)
Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:
SELECT Data from Test_NtextData
FOR XML PATH('')
This will output the data as XML. SSMS allows XML text up to 2GB.
Drew

This works. Interesting how the actual number of characters returned is 43,679 though (regardless of the 65,535 limit). I tried setting the limit to 30,000 and that worked perfectly.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Phil Parkin (12/29/2015)
drew.allen (12/29/2015)
Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:
SELECT Data from Test_NtextData
FOR XML PATH('')
This will output the data as XML. SSMS allows XML text up to 2GB.
Drew
This works. Interesting how the actual number of characters returned is 43,679 though (regardless of the 65,535 limit). I tried setting the limit to 30,000 and that worked perfectly.

Seems to be a bug/issue in SSMS. It was reported (https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode), and closed as not reproducible.

I've reproduced it on all SSMS versions I have starting with 2008 (in 2005 I continue to get the expected number of characters past 43,679 until the maximum) and going to 2014.

For ease of reproducing:

SELECT REPLICATE(CAST('A' AS NVARCHAR(MAX)),65535)

Cheers!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jacob Wilkins (12/29/2015)
Seems to be a bug/issue in SSMS. It was reported (https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode), and closed as not reproducible.

Precisely why I don't even bother with CONNECT except to see what they haven't fixed and won't. That way I know what to do workarounds for. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)