Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storage Size


Storage Size

Author
Message
alok-500895
alok-500895
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 12
Comments posted to this topic are about the item Storage Size
Vivien Xing
Vivien Xing
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1292 Visits: 2204
Name + profession can take max 154?

[Name] NVARCHAR(50) --> 50 X 2 + 2 = 102
Ref BOL: "nchar and nvarchar (Transact-SQL)"
The storage size, in bytes, is two times the number of characters entered + 2 bytes.

sp_help tbPersonalDetails shows total 316 bytes in length.
Anders Pedersen
Anders Pedersen
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 876
Without couting them all up.... there are 150 Nvarchars, so the answer has to be somewhere above 300 bytes....
Manish_
Manish_
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 276
CoulumnName Type Length
---------------------------------------
PK_ID int 4
Name nvarchar 100
Married bit 1
DOB smalldatetime 4
BachelorsDegree bit 1
MastersDegree bit 1
Profession nvarchar 200
Retired bit 1
Age int 4
---------------------------------------
316
---------------------------------------

- Manish
gbn
gbn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 62
Age is not persisted, so it is 312.
But there is the "+2" per variable length column.

So 316... but for the wrong reasons



MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 4208
What's going on with these questions.
Again one with no correct answer to choose from.
I know Steve Jones explained the process of choosing and testing QOTD in his article yesterday and I really don't wanna blame him.
But come on this one today is so obviously wrong, I didn't even bother to pick an answer.

Markus Bohse
Yogeesh
Yogeesh
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 359
I am not seeing the source from where this answer is taken. New way of computations!
skra
skra
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 228
By the way, all bit fields are kept in one byte.

BOL:
"Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11562
Hi all,

I think this is a great question, though thwarted by an incorrect answer, unfortunately.

The question is great because hardly anyone knows exactly how this works. That is shown by the fact that of the "correct" answers posted thus far in this thread, none is actually correct. (And boy, do I expose myself to ridicule now should my answer prove to be wrong as well BigGrin)

But the incorrect answer options result in this QotD being more of a missed opportunity than a learning experience. Sadly.

So, here are the things that are wrong in either the answer provided as "correct", or in the replies I have seen so far.

* NVarchar takes two bytes per character. The QotD misses this, the replies here don't.

* Up to 8 BIT columns are combined in a single bytes. Calculated correctly by QotD, missed by all forum replies.

* Calulcated columns are not stored, unless marked as PERSISTED or included in an index (and in the latter case, they are stored only in the index). Calculated correctly by QotD, missed by most forum replies.

* All varying length columns have a two byte overhead. Missed by the QotD and some of the replies here

* Another two bytes overhead per row are added if a table has at least one varying length column. Missed by all.

* The row also stores a NULL bitmap, one byte for the first 8 columns, one for the next 8, etc. I think that calculated columns don't count (but couldn't find conmfirmation), so that would be 1 byte for the 8 "normal" columns. Missed by all.

* Each row has 4 bytes row header overhead; again missed by all.

* And finally, 2 bytes per row are needed for the row's entry in the slot array, though Books Online does not include this in the calculation for Row_Size.

So we have a maximum data size of 300 bytes for the two nvarchar columns, 4 for the integer, 4 for the smalldatetime, 1 for the 4 bits, and 0 for the (incorrectly) calculated age - max. 309 data size.
We also have some in-row overhead: null bitmap (1), varying length columns (2 because they exist, plus 2 for each varying length column - adding up to 6), and row header overhead (4) - a total of 11 bytes for in-row overhead.
According to the definition of Row_Size in Books Online, that would make for 309 + 11 = 320 bytes per row.

For the real storage requirement per row, we should also include the 2 bytes for the row slot array, so this table will realy take a maximum of 322 bytes of storage per row.

Ref: http://technet.microsoft.com/en-us/library/ms178085.aspx


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4177 Visits: 1648
Randomly choose an answer since all were wrong (25% chance I would choose the "right" one).

Based on BOL "Estimate the size of a heap" (Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4 = 17 + 306 + 4 + 4) the answer should be 331, but I'm most likely also making a calculation mistake Wink
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search