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
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.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis