SQL SERVER Storage of TinyInt

Question

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask.

    USE tempdb ;
    GO

    CREATE TABLE tbl
    (
        i TINYINT NOT NULL
    ) ;
    GO

    INSERT INTO tbl (i)
        VALUES (1) ;
    GO

    DBCC IND ('tempdb','tbl',-1) ;
    GO

    DBCC TRACEON (3604) ; -- Page dump will go the console
    GO

    DBCC PAGE ('tempdb',1,168,3) ;
    GO

Results (I reversed the bytes due to DBCC PAGE's showing the least significant byte first):

Record Size = 9B
10000500 01010000 00
TagA = 0x10 = 1B
TagB = 0x00 = 1B
Null Bitmap Offset = 0x0005 = 2B
Our integer column = 0x01 = 1B
Column Count = 0x0001 = 2B
NULL Bitmap = 0x0000 = 2B (what!?)

Answer 1

If you compute the record using the simple size addition you indeed get 8: 4+1+2+1 (header+fixed size+null bitmap count+ null bitmap itself). But a heap record cannot be smaller than the forwarding stub size, which is 9 bytes, since the record must guarantee that it can be replaced with a forwarding stub. Hence, the record will by actually 9 bytes. A smallint will be 9 bytes both by means of compute and min size. Anything bigger is already bigger than the forwarding stub, so your compute size matches the record size.

Answer 2

It's nice to have the ear of the author. :-) Kalen suspects this is just enforcement of some sort of minimum row length, where anything < 9 is padded to 9. Of course there are only a few cases where this is possible. You will find this phantom byte for TINYINT and BIT as well as VARCHAR(1)/CHAR(1). It won't increase beyond 9 if you move to SMALLINT or CHAR(2), but it will increase if you move to, say, CHAR(3).

So essentially you can point out the efficiencies you can gain by choosing data types wisely, but point out that there are some edge cases where the rules don't hold due to other factors at the storage layer.

EDIT I do hope to have more concrete information for you. Just wanted to let you know that this is what the author of the Internals book currently thinks. She's not 100% certain.