View Single Post
  #10 (permalink)  
Old March 24th, 2009, 07:34 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

Originally Posted by Jeff Moden View Post
Um.... 15 I believe. 12 for the characters, 2 for the character count, and 1 for nullability.
Not to be picky here (OK, maybe a little ), but this isn't quite right.

Varchar columns are stored in a data row after all the fixed length columns. The length of a given varchar column's data is not stored, but rather an array of 2 byte offsets to each column of variable data is stored. Of course, the offsets can be used to easily compute the length of any variable column, but to be precise the length is not explicit.

Whether a column contains NULL is stored in a bitmap, one bit for each column of data (variable or fixed). If the bit is set, the column contains NULL, and no data is stored if the data is a variable type. If the bit is zero, then offsets can be used to locate the data.

Curiously, this technique of not storing the data if a variable column is NULL does NOT apply to fixed length columns. So, if you define a CHAR column of length 50, say, then 50 bytes will be allocated whether or not the column has the value NULL. Zeroes are stored in the data bytes if the fixed length column is NULL, otherwise the actual data is there.

I confess I got this from my 'Inside SQL Server 2000' book, but I'm pretty sure these details have not changed in later releases of SQL Server.
-- Jeff