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 ;

    ) ;

    INSERT INTO tbl (i)
        VALUES (1) ;

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

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

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

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!?)
  • 1
    Is this just educational? I'm all for trimming space where necessary, but this is probably not the 1 byte I'm going to be worried about... Commented Mar 11, 2012 at 17:43
  • This is educational. My next SQLSaturday talk is on compressin; so, I have created examples for every data type to help people understand the implications of their data type choices and to show the affect of compression on all data types.
    – ooutwire
    Commented Mar 11, 2012 at 17:44
  • I assumed that tinyint would be stored as 1B (it is) with 7B of overhead. I wonder what the extra byte is at the end of the record???
    – ooutwire
    Commented Mar 11, 2012 at 17:45
  • I see different results (though not sure if they're more in line with what you expect) when the TINYINT column is not the only column in the table. Seems like a pretty rare use case. Commented Mar 11, 2012 at 17:48
  • Certainly not a common concern of use case. I was just attempting to show each data type alone to drive home both the overhead costs involved in storage and to let beginners see what the column looks like on the page. I find it odd to have the extra byte...drives me nuts to see it there and without reason.
    – ooutwire
    Commented Mar 11, 2012 at 17:52

2 Answers 2


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.

  • The 9 bytes also applies to this definition CREATE TABLE tbl (i TINYINT NOT NULL PRIMARY KEY) so is it just a general rule for all rows whether or not they are part of a heap? Commented Mar 12, 2012 at 17:22
  • 1
    The b-tree can be transformed into a heap (alter table ... drop constraint) and the operation is not a full rebuild (the b-tree upper pages get thrown away, the leaf pages left get unlinked and the result is the heap) so the reservation logic still applies. Commented Mar 12, 2012 at 17:31
  • I think this proves out what Remus has stated...improve.dk/archive/2011/06/07/…
    – ooutwire
    Commented Mar 12, 2012 at 17:54

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.

  • Thank you Aaron for reaching out to Kalen. I was digging through that book last night and pulling my hair out. This is kinda like the extra metadata bytes for sql_variant except here I have no way to explain the phantom byte save for hand waving and shouting "That is the way it is pal!"
    – ooutwire
    Commented Mar 12, 2012 at 17:14
  • 1
    Well you can couple that comment with "this is an extreme edge case, since not many tables are ever designed to try to store a single tinyint or char(1) in each row." Commented Mar 12, 2012 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.