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

score 6 · Accepted Answer · answered Feb 06 '13 at 00:32

6

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.

answered Feb 06 '13 at 00:32

Matt

2,982
1
22
23

Thank you for the explanation. So it appears that in my case it's impossible to reduce the row overhead in SQL Server 2008 to less than 7 bytes. – jirikadlec2 Feb 06 '13 at 00:49

score 1 · Answer 2 · answered Aug 18 '16 at 13:42

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.

Row size overhead

2 Answers2

Linked