nvarchar(100) shows length nvarchar(200) for sp_help

Question

nvarchar(100) shows length nvarchar(200) for sp_help

invaliddba

Default port

Points: 1450
More actions
July 23, 2011 at 10:27 pm

#244793

I ran the below command
create table Ref_organization
( Orgkey int,
Organizationreference nvarchar(100)
The Organizationreference when I run sp_help 'Ref_Organization'
shows nvarchar(200)
Below is the o/p:
Orgkeyintno410 0 yes(n/a)(n/a)NULL
Organizationreferencenvarcharno200 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Why is the length 200?

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

From Books On Line - emphasis added

nchar [ ( n ) ]
Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes

Sp_Help is returning the storage size ....

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

To add to BitBucket's answer... it takes two bytes to represent 1 character in NVARCHAR which is really "Unicode" of sorts. The first byte indicates the "country code" (or dialect) and the second byte indicates the "character code".

That's not an entirely accurate answer because there's so much more to Unicode but it's the easiest to understand answer. 🙂

--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)
Intro to Tally Tables and Functions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Jeff, I figure you were trying to dilute things a bit here, which I do too depending on the forum as it were, but I want to make sure we aren't feeding any myths.

Jeff Moden (7/24/2011)
To add to BitBucket's answer... it takes two bytes to represent 1 character in NVARCHAR which is really "Unicode" of sorts.

"Unicode" is generic and ambiguous. Microsoft diluted the concept for us and now we're stuck with "Unicode" until we peel the onion, which I won't try to do here. I'll just point to Books Online. SQL Server uses the UCS-2 character encoding/set for "Unicode" columns. Read more here.

The first byte indicates the "country code" (or dialect) and the second byte indicates the "character code".

Where can I read more about this little nugget? UCS-2 maps directly to the BMP meaning 2-bytes per character and no surrogate pairs. I don't know of any way to derive the langauge based soley on the value of the first or second byte. Read more about the BMP here.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

opc.three (7/27/2011)
Jeff, I figure you were trying to dilute things a bit here, which I do too depending on the forum as it were, but I want to make sure we aren't feeding any myths.

That's precisely why I said...

That's not an entirely accurate answer because there's so much more to Unicode but it's the easiest to understand answer.

🙂

--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)
Intro to Tally Tables and Functions