SQL Server 2005 int与datetime数据类型的存储结构说明

我经常被问到，或者在论坛上看到，有人提出这样的疑问：为什么SQL Server的int型存的是固定长度4个字节，但实际上能存储远远超过4个”长度”的整型，例如123456789；而日期型是固定长度8个字节，而实际上存储的是类似于” 2011-04-18 20:32:12.540”这种字符长度加起来远远超过8个字节的数据？看起来似乎是一个矛盾的，不可思议的现象。如果你刚好也有这样的疑问，那这篇文章将会回答您的问题。

 

事实上，这是把int型123456789与varchar(或者char)型的”123456789”混为一谈。是的，他们看起来是一样，并且还可以显式/隐式地相互转换，但实际上，SQL Server存储int型与字符型的数据类型，用的是完全不一样的存储结构。同理，存储datetime型与字符型也是完全不同的。本篇文章通过探索SQL Server 2005存储结构，来说明int型与datetime型是如何被存储的。

 

注:本文示范数据库所使用的版本为SQL Server 2005.

 

首先在测试数据库(我的数据库名为Jelly)中建一个测试表test，并插入两条数据：

create table test (

c1 int,                            --c1为int型，存储长度为个字节

c2 datetime default(getdate())     --c2为datetime型，存储长度为个字节

)

 

insert into test(c1) values(1)

insert into test(c1) values(53876254)

 

select * from test

 

SELECT结果如下：

 

 

 

SQL Server以页为单位存储数据。一个页面的大小是8KB。毫无疑问，上面我们所建的这个test表，一个页面完全可以装得下。

 

--使用未文档化的指令DBCC IND找出该表的第一个数据页(返回的结果集中PageType=1那一行)。

DBCC IND ('jelly', 'test', -1);

GO

 

以上DBCC指令输出的结果如下：

 

 

 

请定位到PageType=1那行。PageType=1代表这是个数据页(data page)。PageFID为1，意思是该数据页位于文件号(file number)为1的那个数据文件上；PagePID值为174，指的是该数据页的页编号（page number）。文件号+页编号就能唯一确定SQL Server数据库的一个数据页。

 

接下来使用DBCC PAGE指令输出该页的内容：

--唯有开启追踪旗标3604，DBCC PAGE才能将结果输出到客户端。否则将没有结果集返回。

dbcc traceon (3604)

 

--DBCC PAGE的选项3意为输出最详细的页面内容。

dbcc page(jelly,1,174,3)

 

DBCC PAGE的输出分为三部分：Buffer,PAGE HEADER，以及详细页面数据信息。限于篇幅,我这里只截取了我们今天要详细探讨的DBCC PAGE第三部分的截图。你在结果集输出窗口向下拖动滚动条就可以找到类似的输出。

 

 

 

 

截图中绿色框框标识部分为test表中cl=1的数据行内容；紫色框框标识部分为c1=53876254的数据行内容。

 

红色细框标识部分为每行的dbcc page输出，以十六位的格式显示，每两位代表一个字节。需要特别指出的是，在转换dbcc page十六进位的数据格式之前，需要先以字节为单位，将这些十六进位的数据反转才能读出正确的数据。以0x12c02501为例，读取的方式是这样的：

先以字节为单位将数据分开：12 c0 25 01，然后反转成：01 25 c0 12，这时得才正确的十六位值：0x0125c012.

 

Test表是定长字段的表，也就是说这个表没有可变长度字段， 所以它的存储结构说明如以下excel表格所示：（对于含有可变长度字段的表，存储结构是不一样的。这超出了本文讨论范围。有兴趣者可参考本博客中另一篇<<SQL Server存储结构>>的文章，需要指出的是，那是sql server 2000的，到2005已经略有调整。）

 

 

 

 

说明：

a.       字节0与字节1为SQL Server内部定义的固定结构。

b.       字节2~3表示所有定长字段的位置。SQL Server总是将所有定长字段放在一块儿存储。那么16的值是怎么得来的呢？这是：字节0的长度1+字节1的长度1+字节2~3的长度2+定长字段的总长度12(int 4字节+datetime 8字节)=16.

因此从字节2~3的数据中SQL Server可以知道，该表的定长字段总长度为16-(1+1+2)=12。

至于SQL Server如何从这两个字节的定长字段总长度数字16，区分出每个定长字段的长度，请见本文附录。

c.       字节4~7为存储int型字段值的位置:

excel中所示的0x00000001为c1中的1；

同样的，第二行,c1=53876254 DBCC PAGE的输出，对应的字节4~7的值为：0x0336161e.转换为十进制的值正好为53876254。

d.       字节8~11为存储datetime型字段时间部分值(时:分:秒.毫秒)的位置；

e.       字节12~15为存储datetime型字段年份部分值的位置。

 

 

以下为SQL Server 2005 Books Online中关于datetime型的存储说明：

SQL Server 2005 数据库引擎用两个 4 字节的整数内部存储 datetime 数据类型的值。

第一个 4 字节存储“基础日期”（即 1900 年 1 月 1 日）之前或之后的天数。基础日期是系统参照日期。

另外一个 4 字节存储天的时间，以午夜后经过的 1/300 秒数表示。

由此可知：

a.       字节8~11的十进制值19251218表示，自从当天的00:00:00.000开始，到字段值所示的时间为止，经过了多少个1/300秒；

b.       字节12~15的十进制值40649表示，自从1900年1月1日开始，到字段值所示的日期为止，经过了多少天。

 

 

至止，你应该明白int/datetime型是如何存储的了，也终于明白为什么四个字节的int型能存储超过四个字符长度的数字的原因了。

 

以上说明也同样合适于smallint,tinyint以及smalldatetime。

 

 

附录：

如下查询返回的结果集leaf_offset字段，说明test资料表每个字段的偏移位。SQL Server存储引擎以类似的方式获得资料表每个定长字段的偏移信息。

SELECT  c.name AS column_name, column_id, max_inrow_length, 

         pc.system_type_id, leaf_offset 

 FROM sys.system_internals_partition_columns pc

    JOIN sys.partitions p 

      ON p.partition_id = pc.partition_id

    JOIN sys.columns c

         ON column_id = partition_column_id 

            AND c.object_id = p.object_id

WHERE p.object_id=object_id('test');