行大小开销 [英] Row size overhead

问题描述

我有一个共享托管的MS SQL Server 2008数据库，我需要尽可能减少使用的存储空间。我最大的表具有以下定义：

  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）
  

我试图找出我的表中每个记录的平均字节数
根据理论上大小应该是：4B（行标题）+ 2B（smallint）+ 4B（smalldatetime）+ 2B（smallint）是12字节。

运行命令：

  dbcc showcontig（'stage'）with tableresults 
  pre> 
 
 
它显示：MinimumRecordSize = 15，MaximumRecordSize = 15 
因此根据SQL Server，每个记录的字节数为15而不是12 
每个记录的15个字节似乎也是正确的，当我看看表所占用的总磁盘空间，并除以行数。
 
 
 
什么是占用3额外字节
解决方案
这3个额外的都来自NULL Bitmap。 根据Paul的帖子，它在每一行上保存
 
 
 
并且根据这个BOL post ，NULL Bitmap是= 2 +（（number_columns_in_clustered_index + 7）/ 8） 。在你的情况下，3。
 
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 ???
解决方案
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.

                        
这篇关于行大小开销的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！