Char , Varchar, NVarchar

Question

Char , Varchar, NVarchar

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
August 26, 2014 at 5:43 am

#295759

Hello Experts,
I have defined a Nvarchar column of 30 bytes
Lets say
create table demo (name nvarchar(30))
But when I see the length of this column by selecting table and pressing Alt+F1
Or using col_length
I see length of 60 but it should be 62
However,
Nvarchar(n) length is 2n+2
Varchar(n) length is 2n
Also,
How it works internally?
I mean if Varchar(30) then sql assigns 60 bytes of data but I fill only 20 bytes so doest it takes back 40 bytes of data after column is filled ?

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

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

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

The length from sp_help just shows the max byte size of the column, not the extra 2 bytes needed for a variable length column. Therefore the 60 bytes.

However,
Nvarchar(n) length is 2n+2
Varchar(n) length is 2n

No.

NVarchar(n) requires up to 2n+2

Varchar(n) requires up to n+2

The 2 extra is required to store the actual length of the column.

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Further on Gail's answer, the sp_help simply queries the sys.all_columns returning the max_length as "Length", no information is given on the overhead bytes used. All columns carry some overhead such as NULL bitmap, column offset array (the 2 bytes/var-col), 10 byte row property bitmat etc.. The actual size of the row overhead data varies depending on number of columns, data types, compression and even snapshot isolation affects it.

😎

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

Eirikur Eiriksson (8/26/2014)
... 10 byte row property bitmat etc..

?????

You mean the row header, column and data size entries and offset bytes (which add up to 10 bytes in total)?

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

GilaMonster (8/26/2014)
Eirikur Eiriksson (8/26/2014)
... 10 byte row property bitmat etc..
?????
You mean the row header, column and data size entries and offset bytes (which add up to 10 bytes in total)?

Yes, that would be the version bit, bits for record "type", bitmap exists bit, has var col bit etc.. My point was that it isn't as simple as just adding 2 bytes for each variable column.

😎

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 5

er.mayankshukla (8/26/2014)Hello Experts,

I have defined a Nvarchar column of 30 bytes

Lets say

create table demo (name nvarchar(30))

But when I see the length of this column by selecting table and pressing Alt+F1

Or using col_length

I see length of 60 but it should be 62

However,

Nvarchar(n) length is 2n+2

Varchar(n) length is 2n

Also,
How it works internally?
I mean if Varchar(30) then sql assigns 60 bytes of data but I fill only 20 bytes so doest it takes back 40 bytes of data after column is filled ?

Could you kindly answer this part as well

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

er.mayankshukla (8/26/2014)
er.mayankshukla (8/26/2014)Hello Experts,
I have defined a Nvarchar column of 30 bytes
Lets say
create table demo (name nvarchar(30))
But when I see the length of this column by selecting table and pressing Alt+F1
Or using col_length
I see length of 60 but it should be 62
However,
Nvarchar(n) length is 2n+2
Varchar(n) length is 2n
Also,
How it works internally?
I mean if Varchar(30) then sql assigns 60 bytes of data but I fill only 20 bytes so doest it takes back 40 bytes of data after column is filled ?
Could you kindly answer this part as well

Alt+F1 is a shortcut to the sp_help procedure which returns among other things the maximum length of the column defined by either create or alter table script. It does not show the internal bytes used to track the length of each column within a row of data. Neither does the documentation state that will return anything else.

Data space used for variable length character data types is ([number of characters] x [character size]) + 2 byte entry in the offset array. It is not stored with the variable length data.

😎

ScottPletcher SSC Guru Points: 100950 More actions · Answer 7

er.mayankshukla (8/26/2014)
er.mayankshukla (8/26/2014)Hello Experts,
I have defined a Nvarchar column of 30 bytes
Lets say
create table demo (name nvarchar(30))
But when I see the length of this column by selecting table and pressing Alt+F1
Or using col_length
I see length of 60 but it should be 62
However,
Nvarchar(n) length is 2n+2
Varchar(n) length is 2n
Also,
How it works internally?
I mean if Varchar(30) then sql assigns 60 bytes of data but I fill only 20 bytes so doest it takes back 40 bytes of data after column is filled ?
Could you kindly answer this part as well

nvarchar would contain up to 60 bytes of data (representing 30 characters). But SQL will only use the data bytes it actually needs. For example, if you stored only 5 characters in the column, SQL would only use 10 bytes. The 2-byte overhead is always used, of course, even if the string is NULL or empty.

If you used nchar (rather than nvarchar) then SQL would use reserve all 60 bytes no matter how many bytes of data you actually put into the column. Even empty columns would require 60 bytes of data storage.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.