August 26, 2014 at 5:43 am
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 ?
August 26, 2014 at 6:36 am
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
August 26, 2014 at 7:39 am
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.
😎
August 26, 2014 at 7:42 am
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
August 26, 2014 at 8:13 am
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.
😎
August 26, 2014 at 9:37 am
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
August 26, 2014 at 9:58 am
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.
😎
August 26, 2014 at 1:35 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply