Row size overhead

Question

I have a MS SQL Server 2008 database on a shared hosting and I need to reduce the used storage space as much as possible. My largest table has the following definition:

CREATE TABLE [stage](
    [station_id] [smallint] NOT NULL,
    [time_utc] [smalldatetime] NOT NULL,
    [stage_mm] [smallint] NOT NULL,
CONSTRAINT [PK_stage] PRIMARY KEY CLUSTERED ([station_id] ASC,[time_utc] ASC)

I tried to find out the average number of bytes per record in my table. According to theory the size should be: 4B (row header) + 2B (smallint) + 4B (smalldatetime) + 2B (smallint) which is 12 bytes.

However, when I ran the command:

dbcc showcontig ('stage') with tableresults

It shows: MinimumRecordSize=15, MaximumRecordSize=15 So according to SQL Server, the bytes per record is 15 and not 12 The number 15 bytes per record seems also correct when I look at the total disk space taken by the table and divide it by number of rows.

What is taking up the 3 extra bytes ???

Answer 1

Those 3 extra are from the NULL Bitmap. According to Paul's post, it's on every row save for those that are all SPARSE across the columns (beginning in SQL Server 2008).

And according to a line in this BOL post, the NULL Bitmap is = 2 + ((number_columns_in_clustered_index + 7) / 8). In your case, 3.

Answer 2

I partially agree with @Matt, the 2 bytes are required for NULL bitmap which is correct.

However, the last byte is consumed by the number of columns per bit. Meaning, If I have 6 columns in my table then I'll require 1 byte (6-bits), or If I have 12 columns then I'll require 2 bytes (12-bits).

In your case there are 3 columns hence it has taken just 1 byte.