Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!?)
share|improve this question
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... –  Aaron Bertrand Mar 11 '12 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 Mar 11 '12 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 Mar 11 '12 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. –  Aaron Bertrand Mar 11 '12 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 Mar 11 '12 at 17:52

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
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? –  Martin Smith Mar 12 '12 at 17:22
    
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. –  Remus Rusanu Mar 12 '12 at 17:31
    
Ah thanks. That makes sense! –  Martin Smith Mar 12 '12 at 17:33
    
I think this proves out what Remus has stated...improve.dk/archive/2011/06/07/… –  ooutwire Mar 12 '12 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.

share|improve this answer
    
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 Mar 12 '12 at 17:14
    
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." –  Aaron Bertrand Mar 12 '12 at 17:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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