Geek asks and answers

Top row size

I have a MS SQL Server 2008 database on shared hosting, and I need to reduce the amount of storage space I use 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 figure 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 Thus, according to SQL Server, bytes for writing are 15, not 12. The number of 15 bytes for writing seems correct when I look at the total disk space occupied by the table and dividing it by the number of rows.

What takes 3 extra bytes ???

+5
2

3 NULL Bitmap. , , SPARSE ( SQL Server 2008).

BOL, NULL = 2 + ((number_columns_in_clustered_index + 7)/8). , 3.

+5

@Matt, 2 .

. : 6 , 1 (6 ), 12 , 2 (12 ).

3 , 1 .

+1

More articles:


All Articles