Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Storage Size Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2008 8:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 1:56 AM
Points: 13, Visits: 12
Comments posted to this topic are about the item Storage Size
Post #482000
Posted Tuesday, April 8, 2008 9:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:49 PM
Points: 1,279, Visits: 2,203
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.
Post #482012
Posted Tuesday, April 8, 2008 9:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:58 AM
Points: 1,297, Visits: 765
Without couting them all up.... there are 150 Nvarchars, so the answer has to be somewhere above 300 bytes....
Post #482015
Posted Tuesday, April 8, 2008 11:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 1, 2013 10:14 AM
Points: 286, Visits: 252
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
Post #482059
Posted Wednesday, April 9, 2008 12:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 27, 2010 3:05 PM
Points: 1,629, 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



Post #482063
Posted Wednesday, April 9, 2008 12:34 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,432, Visits: 4,171
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
Post #482075
Posted Wednesday, April 9, 2008 12:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 6, 2012 10:56 PM
Points: 180, Visits: 359
I am not seeing the source from where this answer is taken. New way of computations!
Post #482080
Posted Wednesday, April 9, 2008 12:57 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:08 AM
Points: 711, Visits: 218
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."
Post #482084
Posted Wednesday, April 9, 2008 1:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
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 :D)

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
Post #482086
Posted Wednesday, April 9, 2008 1:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 3,236, Visits: 1,265
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 ;)
Post #482087
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse