SQL Server中表的行大小的差异

CREATE TABLE [dbo].[Account]
(
     [AccountId] [int] IDENTITY(1,1) NOT NULL,
     [RowVersion] [timestamp] NOT NULL,
     [Deleted] [bit] NOT NULL CONSTRAINT [DF_Account_Deleted]  DEFAULT ((0)),
     [CurrentBalance] [decimal](19, 4) NOT NULL DEFAULT ((0)),
     [ExtrasCurrentBalance] [decimal](19, 4) NOT NULL DEFAULT ((0)),

     CONSTRAINT [PK_Account] 
         PRIMARY KEY CLUSTERED ([AccountId] ASC)
) ON [PRIMARY]

As per the logic in https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-heap

根据https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-heap中的逻辑

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

So my row size should be

所以我的行大小应该是

(6+8+1+9+8)+3+4 = 39

But when executing this query

但是在执行此查询时

DBCC SHOWCONTIG ('dbo.Account') with tableresults

I see MinimumRecordSize and MaximumRecordSize as 52.

我看到MinimumRecordSize和MaximumRecordSize为52。

So how did we get a value of 52 bytes?

那我们怎么得到一个52字节的值?

Thanks, Pavan

1 个解决方案

#1

I believe you have row versioning active in your database, then it makes sense.

我相信你的数据库中有行版本控制,然后才有意义。

You can check with this query:

您可以查看此查询:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state
FROM sys.databases;

If either flag is on for the database you are working with then you have row versioning, which by itself adds 14 bytes to your row size.

如果您正在使用的数据库中有任一标志,那么您就有行版本控制,它本身会为您的行大小添加14个字节。

So 52 breaks down as follow:

所以52分解如下:

7 bytes for overhead (number of columns, internal flags, null bitmap, row size, etc);

开销为7个字节(列数,内部标志,空位图,行大小等);

14 bytes for row versioning;

用于行版本控制的14个字节;

31 bytes for your columns (int = 4 bytes, timestamp = 8 bytes, bit = 1 bit per bit, rounded up to whole bytes, decimal(19) = 9 bytes);

列的31个字节(int = 4个字节,timestamp = 8个字节,bit =每位1位,四舍五入到整个字节,十进制(19)= 9个字节);

#1