Column declaration

Question

Column declaration

chrism 16498

SSC Enthusiast

Points: 124
More actions
August 22, 2011 at 4:15 am

#144297

Hi guys,
I've been working with SQL server for past 7 yrs now but have only recently started getting more interested in advanced functionality. Anyway I was going through the 70-431 training kit and came across something interesting that I would like clarified. Below is of the the book
'For Example, Char(10) can store a maximum of 10 characters because each character requires one bytes of storage, whereas a nchar(10) can store a maximum of five characters because each Unicode character requires two bytes of storage'
The above statement does appear to be true. I created a nchar(10) column and populated it with 8 characters.
Which leads to my question is the number in the brackets after the data type the size in bytes or number of characters to be stored in the column? I always thought it was the number of characters not the number of bytes and if it is the number of bytes how can I put 8 characters into a nchar(10) that should only accept 5?

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

It's explained reasonably well in BOL.

n is the number of characters, which for CHAR(n) datatype is the same as the storage requirement in bytes. The storage requirement is 2xn bytes for NCHAR(n).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

The book is wrong. The number that is used when you declare a variable or define a column that is a Unicode, specifies the number of characters and not the number of bytes that is being used. The code bellow shows it. By the way the fact that you could populate an nvarchar(10) column with string that has 8 characters, shows that the limit is more then 5 characters

declare @Unicode nvarchar(2)

--Since it was declared with the number 2, it should get both charcters

set @Unicode = N'ab'

select @Unicode

select @Unicode as UnicodeValue, len(@Unicode) as NumOfCharacters, datalength(@Unicode) as NumOfBytes

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 3

And that's why you only store 4000 some odd characters instead of 8000 some odd without resorting to using MAX. That's for nchar & nvarchar.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Adi Cohn-120898 (8/22/2011)
The book is wrong...

Quote from BOL, SQL server 2k8:

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

ChrisM@Work (8/22/2011)
Adi Cohn-120898 (8/22/2011)
The book is wrong...
Quote from BOL, SQL server 2k8:
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.

My quote was not about BOL. I was commenting about the book that the original poster was quoting:

'For Example, Char(10) can store a maximum of 10 characters because each character requires one bytes of storage, whereas a nchar(10) can store a maximum of five characters because each Unicode character requires two bytes of storage'

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

chrism 16498 SSC Enthusiast Points: 124 More actions · Answer 6

Adi Cohn-120898 (8/22/2011)
The book is wrong. The number that is used when you declare a variable or define a column that is a Unicode, specifies the number of characters and not the number of bytes that is being used. The code bellow shows it. By the way the fact that you could populate an nvarchar(10) column with string that has 8 characters, shows that the limit is more then 5 characters
Adi

The book is wrong... great publication Microsoft, way to know your own product.

I really only needed some clarification as what you have said is what I believed (and yes I populated 8 characters into an nchar(10) which the book said could only handle 5) but its a Microsoft book and its for the 70-431 exam

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Adi, you are of course absolutely correct. Sorry for the confusion.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden