varchar to nvarchar

Question

varchar to nvarchar

Anamika

SSCertifiable

Points: 6202
More actions
February 17, 2012 at 7:47 am

#391583

hi,
while changing the varchar to nvarchar on my tables to support unicode, do i have to increase the size of the datatype?
for example to change varchar(40) if i change into nvarchar(40) is sufficient or i have to chage into nvarchar(80)?
thanks,
regards,
ami

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

An nvarchar(40) holds 40 characters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.

and its length is 80 bytes, to accomodate unicode...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...

Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 4

Anamika (2/17/2012)
hi,
while changing the varchar to nvarchar on my tables to support unicode, do i have to increase the size of the datatype?
for example to change varchar(40) if i change into nvarchar(40) is sufficient or i have to chage into nvarchar(80)?
thanks,
regards,
ami

VARCHAR takes 1 byte space (on disk) to store 1 character whereas NVARCHAR takes 2 byte space (again on disk) to store 1 character. Therefore, you don't need to increase your datatype length. Both VARCHAR(40) & NVARCHAR(40) will store 40 characters (at max).

However, if you are going to make the same change in many of your tables/columns obviously you need more space on disk.

Sujeet Singh

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...
Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.

Of cause! :w00t:

Maximum 80 bytes for value + 2 bytes to store actual size.

I wonder how long this thread can be made? :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

VARCHAR takes 1 byte space (on disk) to store 1 character whereas NVARCHAR takes 2 byte space (again on disk) to store 1 character.

This is not quite correct. Eugene has (finally!) the correct answer 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Phil Parkin (2/17/2012)
VARCHAR takes 1 byte space (on disk) to store 1 character whereas NVARCHAR takes 2 byte space (again on disk) to store 1 character.
This is not quite correct. Eugene has (finally!) the correct answer 🙂

lol 😀

Sujeet Singh

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...
Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.
Of cause! :w00t:
Maximum 80 bytes for value + 2 bytes to store actual size.
I wonder how long this thread can be made? :hehe:

I cannot take it!

Gail did it!

She gave enough information in her first reply

I am just bored by end-of-friday-before-going-home time :hehe:

Anyone for page two?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Divine Flame SSCoach Points: 15943 More actions · Answer 9

Eugene Elutin (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...
Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.
Of cause! :w00t:
Maximum 80 bytes for value + 2 bytes to store actual size.
I wonder how long this thread can be made? :hehe:
I cannot take it!
Gail did it!
She gave enough information in her first reply
I am just bored by end-of-friday-before-going-home time :hehe:
Anyone for page two?

Ofcourse, why not! on your special demand:-)

Yes, Gail had given the exact answer in her first reply (as she always does!). What me OR you were trying to do was explaining in some layman terms (I think so) & making Ami aware that she may run out of space if applied to many tables/columns at once without prior analysis.

Sujeet Singh

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

I knew! We can make it!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...
Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.
Of cause! :w00t:
Maximum 80 bytes for value + 2 bytes to store actual size.

<pedantic> The 2 bytes isn't the length, it's the pointer to position of the end of the column within the variable length column array. The length can be calculated from it and either the end of the offset array or the next offset pointer. </pedantic>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
Eugene Elutin (2/17/2012)
GilaMonster (2/17/2012)
An nvarchar(40) holds 40 characters.
and its length is 80 bytes, to accomodate unicode...
Its length is anything from 2 bytes to 82 bytes depending on the data stored in the column. An nchar(40) would have a length of exactly 80 all the time.
Of cause! :w00t:
Maximum 80 bytes for value + 2 bytes to store actual size.
<pedantic> The 2 bytes isn't the length, it's the pointer to position of the end of the column within the variable length column array. The length can be calculated from it and either the end of the offset array or the next offset pointer. </pedantic>

I need to ask it:

If it's "the pointer to the position of the end of the column within the variable length column array" can it have other value than the length of the data stored within column? Let say column is varchar(40), the data i store there is 'aaa', what the pointer will contain? 3 or 4 or something totally else? How this pointer looks like?

The maximum integer number two bytes can hold is 65025, so what happens with VARCHAR(MAX)?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Eugene Elutin (2/17/2012)
If it's "the pointer to the position of the end of the column within the variable length column array" can it have other value than the length of the data stored within column?

Certainly

Let say column is varchar(40), the data i store there is 'aaa', what the pointer will contain? 3 or 4 or something totally else? How this pointer looks like?

Depends on whether there are other variable-length columns in that table and, if so, what position this one is at within the row structure. I'm sure Paul Randal has a blog post or two on row structures.

The maximum integer number two bytes can hold is 65025, so what happens with VARCHAR(MAX)?

Well the maximum size of a row (on-page) is 8060 bytes because of the size of a page (8196 bytes), so the offset can't ever be higher than the (max size of a row) - (other columns, row header, null bitmap, etc)

And the max size of an unsigned 2-byte integer is 65535 (2¹⁶-1)

As for how LOBs are stored, maybe an exercise for the reader? 😉 (Paul Randal's blog should have some stuff, Books Online, SQL Server 2008 Internals by Kalen Delaney)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 14

Thank You, Gail!

Finally OP can make fully qualified decision of what size he need for his nvarchar! :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]