Storage Size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by alok » Storage Size

58 posts, Page 1 of 6

1 2 3 4 5 »»»

Storage Size

Rate Topic

Display Mode

Topic Options

Author

Message

alok-500895

Posted Tuesday, April 08, 2008 8:49 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 04, 2009 1:56 AM
Points: 13, Visits: 12

Comments posted to this topic are about the item Storage Size

Post #482000

Vivien Xing

Posted Tuesday, April 08, 2008 9:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:19 AM
Points: 1,279, Visits: 2,191

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

Anders Pedersen

Posted Tuesday, April 08, 2008 9:05 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,200, Visits: 645

Without couting them all up.... there are 150 Nvarchars, so the answer has to be somewhere above 300 bytes....

Post #482015

Manish_

Posted Tuesday, April 08, 2008 11:54 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 01, 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

gbn

Posted Wednesday, April 09, 2008 12:03 AM

SSCommitted

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

MarkusB

Posted Wednesday, April 09, 2008 12:34 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:22 AM
Points: 4,218, Visits: 3,875

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

Yogeesh

Posted Wednesday, April 09, 2008 12:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 06, 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

skra

Posted Wednesday, April 09, 2008 12:57 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:11 AM
Points: 711, Visits: 210

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

Hugo Kornelis

Posted Wednesday, April 09, 2008 1:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

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

Mighty

Posted Wednesday, April 09, 2008 1:00 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:33 AM
Points: 2,723, Visits: 987

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 »

58 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions