Dr. Diana Dee (4/9/2008)
Hugo is almost correct.
Hi Diana,
That is true. But not for the reason you mention. My mistake was forgetting the two bytes to store the number of variable length columns. I first noticed this when I saw the post by Steven Cameron, who did include these two bytes.
He put in one byte for the nulls. None of the columns allow nulls.
Whether columns allow NULLS or not is not relevant. The NULL bitmap is always stored. This is documented in Books Online, and in Kalen Deaney's "Inside SQL Server 2005: The Storage Enngine". Plus, I just confirmed this by testing.
I get a maximum length of 319.
Which is 3 bytes short of the actual amount. One for the NULL bitmap, and two for the number of columns (which is, for some weird reason, counted as part of the NULL bitmap in BOL).
If there were a column allowing null, there would be a 2-byte block to store the number of null columns followed by a null bitmap indicating whether each individual column is null (1 byte per 8 columns). So if just 1 column allowed a null, you would have to add 3 bytes to the above.
Reference: Microsoft course 2279 module 2 page 11.
I happen to have that course book (it's marked as 2279-A, but I assume it's the same). The page you reference does describe the NULL bitmap as being two bytes for the number of columns plus 1 byte for each grop of up to 8 columns, but it doesn't say that the NULL bitmap is omitted if no nullable columns exist. I believe that this has been the case in earlier versions of SQL Server, but not in SQL Server 2005 (and I think not in SQL Server 2000 either, but I'm not sure of that).
Anyway, since I'm still not sure whether the computed column is included in the NULL bitmap, I decided to run a quick test, creating the table, inserting some rows, and examining the actually stored data by running DBCC PAGE. Here is the output for the row with all variable length columns filled with maximum length:
[font="Courier New"]DECLARE @DB varchar(20)
SET @DB = DB_NAME();
DBCC PAGE (@DB,1,45,3); -- 45 is the page for this table (based on sysindexes), 3 is verbose output
(snip)
Slot 2 Offset 0xb2 Length 322
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x6135C0B2
00000000: 30000d00 03000000 9f0000ac 8e080000 †0...............
00000010: 02007a00 42016100 61006100 61006100 †..z.B.a.a.a.a.a.
00000020: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000030: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000040: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000050: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000060: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000070: 61006100 61006100 61006200 62006200 †a.a.a.a.a.b.b.b.
00000080: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000090: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000A0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000B0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000C0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000D0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000E0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000F0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000100: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000110: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000120: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000130: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000140: 6200†††††††††††††††††††††††††††††††††b.
Slot 2 Column 0 Offset 0x4 Length 4
PK_ID = 3
Slot 2 Column 1 Offset 0x16 Length 100
Name = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Slot 2 Column 2 Offset 0x8 Length 1 (Bit position 0)
Married = 1
Slot 2 Column 3 Offset 0x9 Length 4
DOB = Jan 1 2000 12:00AM
Slot 2 Column 4 Offset 0x8 Length 1 (Bit position 1)
BachelorsDegree = 1
Slot 2 Column 5 Offset 0x8 Length 1 (Bit position 2)
MastersDegree = 1
Slot 2 Column 6 Offset 0x7a Length 200
Profession = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
Slot 2 Column 7 Offset 0x8 Length 1 (Bit position 3)
Retired = 1 [/font]
I have highlighted some points of interest:
* Length 322 - This confirms my latest computation, corrected after Steven's post;
* 0800 - The number of columns (remember that SQL Server swaps the bytes, so this is actually the value 0x0008, or 8 in decimal). This confirms that the computed column is not counted.
* 00 - The actual NULL bitmap. I also ran a test with a persisted calculated column instead of a non persisted one, and that did indeed cause the number of columns to go up to 9 and the NULL bitmap to take an extra byte.
* 0200 - The start of the variable length portion. These first two bytes is the number of variable length columns, again byte-reversed - 0x0002, or 2 in decimal. After that follow two sets of two bytes with the end position of each of the varying length values: 0x007a (122) and 0x0142 (322).
Not included in this output are the two bytes each row needs in the row offset table at the end of the page, but we can see that by changing the output type to 2. The end of the results then look like this:
[font="Courier New"]6117DFE0: 4410f40f ac0fc408 78082c08 e0079407 †D.......x.,.....
6117DFF0: 50071007 c8068006 4006b200 70006000 †P.......@...p.`.
OFFSET TABLE:
Row - Offset
2 (0x2) - 178 (0xb2)
1 (0x1) - 112 (0x70)
0 (0x0) - 96 (0x60) [/font]
The 6 bytes for the three rows in the offset table have, once more, been marked by me.