Sql-server – SQL SERVER Storage of TinyInt

sql-server-2008

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!?)

Best Answer

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.

Related Question