SQL Server 存储(2/8)：理解数据记录结构

在SQL Server ：理解数据页结构我们提到每条记录都有7 bytes的系统行开销，那这个7 bytes行开销到底是一个什么样的结构，我们一起来看下。

数据记录存储我们具体的数据，换句话说，它存在堆表里，或者存在聚集索引的叶子节点。数据记录结构是为了让SQL Server更高效的管理数据。我们来看下数据记录结构示意图：

上图中蓝色部分是所有数据记录部分（即系统行开销，大小基于列个数，等于或大于7 bytes），绿色部分是表结构里取决于定长/变长列的数据记录部分（实际存放的数据，大小基于实际数据）。

行头系统数据：

用做状态位1的第1字节（8位）是用来定义记录的属性：

• 第0位：版本信息，在SQL Server 2008里始终是0；
• 第1-3位：这3位用来定义记录类型；
  • 0 数据记录（data record）
  • 1 转发记录（Forwarded record）
  • 2 转发存根（a forwarding stub）
  • 3 索引记录（Index record）
  • 4 二进制堆碎片或行溢出数据（blob fragment or row overflow data）
  • 5 鬼影索引记录（ghost index record）
  • 6 鬼影数据记录（ghost data record）
  • 7 鬼影版本记录（ghost version record）
• 第4位：存在空值位图（Null bitmap ）或没有。在SQL Server 2008里没有不为空的列也会有空值位图（Null bitmap ）；
• 第5位：表示是否存在变长列；
• 第6位：表示该列包含版本信息；
• 第7位：在SQL Server里未使用；

用作状态位2的第2字节（8位）。只有1位用来表示这条记录是否为鬼影转发记录（ghost forwarded record）。

由行头开始到定长列结尾长度：

下2个字节用来存储行头开始到定长列结尾长度。它包含2个状态位，2个字节用作这个列表示在表中定长数据的实际长度。例如如果表里没有定长列，这个列的值会是4。这和页头列pminlen显示的值是一样的。

所有定长列字段值（Fixed_Data_Size）：

下n个字节用来存储在表中的定长数据，n就是在表中所有定长列的长度。如果表里的所有列都是变长列，这一部分就没有。

空值位图（Null_Bitmap）：

下2个字节用来存储表里的列数。

下n个字节用作空值位图，每个bit对应一个列，1表示对应列为空。n的值为：列数 / 8，将值取整。

Variable_Data_Size:

下2个字节用来存储表里变长列个数。

下n个字节用来存储每个变长列结束为止的偏移量。每个变长列需要2字节，n的值为：变长列数 * 2 。

最后n个字节用来存储所有变长列值，n的值为所有变长列的实际长度的总长度。

 我们来看一个具体的例子：

创建数据库，并插入2条记录

USE [InternalStorageFormat] 2 GO 3  4 IF EXISTS ( SELECT  * 5             FROM    sysobjects 6             WHERE   id = OBJECT_ID(N'[dbo].[Customers]') 7                     AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 8     DROP TABLE dbo.Customers 9 10 CREATE TABLE Customers11 (12    FirstName CHAR(50) NOT NULL,13    LastName CHAR(50) NOT NULL,14    Address CHAR(100) NOT NULL,15    ZipCode CHAR(5) NOT NULL,16    Rating INT NOT NULL,17    ModifiedDate DATETIME NOT NULL,18 )19 GO20 21 22 INSERT INTO dbo.Customers23         ( FirstName ,24           LastName ,25           Address ,26           ZipCode ,27           Rating ,28           ModifiedDate29         )30 VALUES  ( 'Woody' , -- FirstName - char(50)31           'Tu' , -- LastName - char(50)32           'ZUOQIAO YOUXI TOWN LINHAI CITY' , -- Address - char(50)33           '0000' , -- ZipCode - char(5)34           1 , -- Rating - int35           '2015-05-07 10:09:51'  -- ModifiedDate - datetime36         )37         go 2

使用DBCC IND命令查看表对应页列表：

DBCC IND('InternalStorageFormat','Customers',-1)

我们看到数据页号为79。

使用DBCC PAGE命令查看页信息：

DBCC TRACEON(3604)2 DBCC PAGE(InternalStorageFormat,1,79,3)3 GO  

在页头pminlen的值是221，包括定长列的总长217 bytes（50+50+100+5+4+8），2 bytes用作状态位（行头系统开销），2 byte 用作由行头开始到定长列结尾长度。

在记录槽提到的长度224，包括页头pminlen的值，1 byte用作空值位图（6/8 取整为1）和2 bytes 的字段个数。

我们来看一个变长列的表。

创建表并插入数据后，查看表对应的页：

CREATE TABLE VariableLength( 2    Title         CHAR(10) NOT NULL, 3    FirstName     VARCHAR(100), 4    Lastname      VARCHAR(100), 5    email         VARCHAR(50),  6    dob           date NOT NULL, 7    phone         CHAR(10), 8    Countrycode   CHAR(3), 9    Designation   VARCHAR(100),10    PersonalPreference VARCHAR(100)11 )12 GO13 INSERT INTO VariableLength VALUES ('Mr','Woody','Tu','smartgz@qq.com','2015-5-7','XXXXXXXXXX','Chn','DBA','Nothing Spl')14 GO15 DBCC IND('InternalStorageFormat','VariableLength',-1)

我们看到数据页号为202。

使用DBCC PAGE命令查看页信息：

DBCC TRACEON(3604)2 GO3 DBCC PAGE('InternalStorageFormat',1,202,3)--记得根据你的实际数据库，修改页号202

pminlen值为30，包含：

• 1 byte 状态位1
• 1 byte 状态为2
• 2 bytes 存储行头开始到定长列结尾长度
• 26 bytes 所有定长列总长度（10+3+10+3：tittle，dob，phone，countrycode）

  • Title  CHAR(10) NOT NULL

  • dob date NOT NULL

  • phone CHAR(10)

  • Countrycode CHAR(3)

可以用下列语句验证下定长列总长度： 

SELECT DATALENGTH(Title) title,DATALENGTH(dob) dob,DATALENGTH(phone) phone,DATALENGTH(Countrycode) countrycode FROM VariableLength

 在槽0显示的81长度包含：

• 1 byte 状态位1
• 1 byte 状态为2
• 2 bytes 存储行头开始到定长列结尾长度
• 26 bytes 所有定长列总长度（10+3+10+3：tittle，dob，phone，countrycode）

  • Title  CHAR(10) NOT NULL

  • dob date NOT NULL

  • phone CHAR(10)  

  • Countrycode CHAR(3)

• 2 bytes 存储列个数
• 2 bytes 用作空值位图，字段个数/8后取整，即 9/8 得到2
• 2 bytes 存储变长列个数
• 10 bytes 用来存储每个变长列结束位置的偏移量 变长列个数 * 2，即 5 * 2 得到10，5个变长列包含：

  • FirstName VARCHAR(100)

  • Lastname VARCHAR(100)  

  • email VARCHAR(50)

  • Designation VARCHAR(100)  

  • PersonalPreference VARCHAR(100)

• 35 bytes 用来存储所有变长列的实际长度，这个可以使用下列语句得到

SELECT DATALENGTH(FirstName)+DATALENGTH(Lastname)+DATALENGTH(email)+2 DATALENGTH(Designation)+DATALENGTH(PersonalPreference) FROM VariableLength

总结下每条记录的系统行开销：

行头系统数据（2 bytes）+由行头开始到定长列结尾长度（2 bytes）+列个数（2 bytes）+空值位图数据（取整（列个数/8） n bytes）

即 2 bytes + 2 bytes + 2 bytes + 取整（列个数/8）

当列个数小于等于8时，系统行开销始终是7 bytes，往上没增加8列，增加1 bytes，即系统系统行开销始终大于等于7 bytes。

对于在SQL Server里数据记录的存储格式，希望你已经有了清晰的认识。

 参考文章：

http://www.sqlservercentral.com/blogs/practicalsqldba/2012/08/22/sql-serverunderstanding-the-data-record-structure/