SQL SERVER 2005页面存储之--表的数据行在页中的存储

SQL SERVER 2005页面存储之--表的数据行在页中的存储 

 

第一篇来说说我们的表的数据行在页中的存储详解,也为接下来几篇关于特殊数据类型存储和索引的存储

这篇讲得是没有聚集索引,没有非聚集索引,含有定长列和变长列的堆

 

/*----------------------------------------------------------------------

*auther:Poofly

*date:2010.3.14

*VERSION:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

Jul  9 2008 14:43:34 

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )

*转载请注明出处

*更多精彩内容,请进http://blog.csdn.net/feixianxxx

------------------------------------------------------------------------*/

--建立测试表

create table t_test

(

col1 int,

col2 varchar(10),

col3 datetime,

col4 char(10),

col5 nvarchar(4)

)

go

--插入数据

insert t_test values(1,'ABC','2010-03-15','123',N'abc')

insert t_test values(2,'DEF',CURRENT_TIMESTAMP,'4567',N'defg')

go

--接下来说说读取信息的一般步骤:

 

1.我们要查看页面分布情况,可以使用DBCC IND('dbname'|dbid,'objectname'|objectID,nonclustered indid|1|0|-1|-2[,partition_number])

 

--找到各个类型的页面分布和它们的所在的文件号和页号.

      {'dbname'|dbid}-数据库名/数据库ID

      {'objectname'|objectID}->对象名/对象ID

     {nonclustered indid|1|0|-1|-2}->0 显示行内数据分页及指定对象的行内IAM分页信息

                                                 1 ：显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页

                                                -1： 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.

                                               -2： 显示指定对象的所有IAM分页

                                               非聚集索引的ID ：显示所有的IAM、数据分页以及一个索引的索引分页信息。

     {partition_number}->可选,为了与中的DBCC IND命令向前兼容.它指定了一个特定分区号,如果不指定,显示所有分区的信息

 

  --介绍完后,我们来使用这个DBCC IND的命令

  DBCC IND(poofly,t_test,1)

  --我们发现输出了很多的列名,这样的输出无法让我们有所选择过滤自己想要的.所以采取技术内幕上所用的insert exec的方法将信息导入表中后进行查看(这也是后几篇将用到的手段)

  --首先创建信息表(这个表之后几篇会一直用到)

  CREATE TABLE sp_table_pages

(PageFID  tinyint, 

  PagePID int,   

  IAMFID   tinyint, 

  IAMPID  int, 

  ObjectID  int,

  IndexID  tinyint,

  PartitionNumber tinyint,

  PartitionID bigint,

  iam_chain_type  varchar(30),    

  PageType  tinyint, 

  IndexLevel  tinyint,

  NextPageFID  tinyint,

  NextPagePID  int,

  PrevPageFID  tinyint,

  PrevPagePID int, 

  Primary Key (PageFID, PagePID));

GO

--接着将输出结果导入表中

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

EXEC ('DBCC IND(poofly,t_test,1)'  );

GO

SELECT PageFID, PagePID, indexID,IndexLevel, PageType FROM sp_table_pages

/*

PageFID PagePID     indexID IndexLevel PageType

------- ----------- ------- ---------- --------

5       1119        0       0          1

5       1168        0       NULL       10

*/

--在我们的输出结果中可以看到我们的文件号及对应的页号，当然也看到了页面的类型。

   indexID->0 代表堆, 1 代表聚集索引的分页, 2-250 代表非聚集索引的分页

   IndexLevel->索引的层次 0 代表叶级别分页 >0 代表非叶级别层次 NULL 代表IAM分页

   PageType->分页类型 1=数据页面 2=索引分页 3=LOB_MIXED_PAGE 4=LOB_FREE_PAGE 10=IAM分页

    所以上面的第一条记录就表示号文件页是一个堆上的在叶级别的数据页

 

 

 

2.运行DBCC TRACEON(3604) 这样才能让DBCC PAGE的结果输出给客户端。

DBCC TRACEON(3604)

 

3.使用DBCC PAGE({dbid|dbname},filenum,pagenum[,printopt])命令读取你想要查看的页面

关于这个命令的详细讲解,可以参看石头大大的写的http://blog.csdn.net/happyflystone/archive/2009/11/19/4834762.aspx

 

  --简单介绍下页面的组成

一般的页面分成个部分:BUFFER、PAGEHEADER、DATA、OFFSET TABLE

BUFFER:显示了指定页面的缓冲信息。由于它是一个内存中结构,所以仅当页面处于内存中时候才有效.

PAGEHEADER:显示指定页面的所有报头字段信息。具体内容参考影子老师写的http://blog.csdn.net/HEROWANG/archive/2009/12/11/4987066.aspx

DATA:显示每行数据的具体存储.这里使用的Printopt不一样,显示的风格也不一样。下面重点具体分析该部分的数据读取部分.

            有关该部分显示的行类型的解读可以看石头大大写的http://blog.csdn.net/happyflystone/archive/2009/12/09/4969909.aspx。

OFFSET TABLE:显示了所有行偏移矩阵的内容.

 

说了这么多,我们具体来看看我们文章中这个表的页面吧

dbcc page('poofly',5,1119,1)

/*

PAGE: (5:1119)

BUFFER:

<这里的内容就是BUFFER>

PAGE HEADER:

<这里的内容就是PAGE HEADER>

..我们主要是分析下面的DATA部分

DATA:

 

Slot 0, Offset 0x60, Length 44, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Record Size = 44                     

Memory Dump @0x6301C060

 

00000000:   30001a00 01000000 00000000 3a9d0000 †...........:...         

00000010:   31323320 20202020 20200500 00020026 †23       .....&         

00000020:   002c0041 42436100 62006300 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.,.ABCa.b.c.             

 

Slot 1, Offset 0x8c, Length 46, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Record Size = 46                     

Memory Dump @0x6301C08C

 

00000000:   30001a00 02000000 121eba00 3c9d0000 †...........<...         

00000010:   34353637 20202020 20200500 00020026 †567      .....&         

00000020:   002e0044 45466400 65006600 6700†8224 ?†8224 ?†8224 ?...DEFd.e.f.g.           

 

OFFSET TABLE:

 

Row - Offset                         

1 (0x1) - 140 (0x8c)                 

0 (0x0) - 96 (0x60)                  

*/

 

准备工作1:应该了解下数据行的结构(索引行的结构和这个是不一样的)

状态位A 状态位B 列数目的位置   定长数据长度  表总列数  NULL位图     变长列的列数 第一变长列数据终止位置 第二... 变成列的实际数据长度

 1B            1B               2B                      xB                  2B   CEILING(列数/8)     2B                           2B                             2B                xB

 ps:NULL位图后面的几个字节只有当表中含有变长列的时候才会有

 

再看看我们的数据===(1,'ABC','2010-03-15','123',N'abc') 对照着上面的结构我们来算下第一行的行长(Slot 0, Offset 0x60, Length 44, DumpStyle BYTE)

                        1+1+2+(4+8+10)+2+ceiling(5/8)+2+2+2+3+3*2=44

 

 准备工作2:我们应该了解这里读的方法

          1.这里的每个数字就是以十六进制存储的,也就是说个这里的数字就代表一个字节,我们一般采取把十六进制换成二进制,然后再换成十进制读取

          2.凡是存储的是字符类型的数据值,你读的时候就按顺序,一个字节一个字节读

          3.存储datetime or 数字类型 内部采取先存储低位字节 我们就采取 交换字节读取(例如 0009 换成)

         4.如果遇到状态位 NULL位图等 就采用顺序读取,当然这里的BIT位是从高到底 比如->00000001 右边的是BIT0

 

 

 

  关于DATA部分Record Type (行类型部分)的解析请看石头博客http://blog.csdn.net/happyflystone/archive/2009/12/09/4969909.aspx

 

 下面开始具体拆分我们的数据行

 

 状态位A:30->00110000 它是一个行属性的位图 从高位存到地位(右边第一位是bit0).

         bit0:版本信息,SQL2005/08总是为

         bit1-3: 0=(primary record);1=(forwarded record);2=(forwarding stud);3=(index record);4=(溢出数据);5=(ghost索引记录);6=(ghost数据记录)

         bit4:表示存在NULL位图(在数据行里SQL2005/08总存在NULL位图)

         bit5:表示存在变长列

         bit6:未启用

         bit7:表示存在幽灵记录

         回到我们的例子中,我们的bit4为 bit5为表示存在NULL位图及存在变长列

 

状态位B:00->未启用

 

列数的出现位置:1a00->00000000 00011010=26<就是出现的位置>

 

定长数据部分(col1,col3,col4):01000000->00000000 00000000 00000000 00000001=1<col1的值,4字节>

                                                   00000000 3a9d0000->1001110100111010 0...0 =40250 <col3值,8字节>

                                                   31323320 20202020 2020->'123       '<col4的值,char(10),10字节>

 

            ps:注意这里的col3的类型是datetime类型,这个类型在内部存贮并不是你们想象的那样.

               我简单来说就是它将这个类型的存储分成个部分,date部分和time部分 

               我们的例子中 表示的时间部分,3a9d0000表示的时候date部分

               因为第一部分time默认是:00:00 所以值为 3a9d0000换算成十进制为

               具体参考我以前写的http: //blog.csdn.net/feixianxxx/archive/2009/10/15/4677124.aspx 第三点

               你可以通过

               select CONVERT(int,SUBSTRING(CONVERT(varbinary(8),cast('2010-03-15' as datetime)),1,4)),--40250

              CONVERT(int,SUBSTRING(CONVERT(varbinary(8),cast('2010-03-15' as datetime)),5,4))--0

       --自己可以尝试第二条记录验证换算

                            

表总列数:0500->00000000 00000101=5 表示该表有列

 

NULL位图:00->00000000 因为该表只有列 所以只需要看后面个,0表示该行的对应列不为NULL 

 

变长列的数目:0200->00000000 00000010=2 表示该表有列 (col2,col5)

 

第一变长列数据终止位置:2600->00000000 00100110=38=1+1+2+(4+8+10)+2+ceiling(5/8)+2+2+2+len('ABC')

 

第二变长列数据终止位置:2c00->00000000 00101100=44 因为一共就列变长列 所以它的结束位置就是行的最终长度 LENGTH 44

 

第一列变长列的数据:41 4243->01000001 01000010 01000011='ABC'

 

第二列变长列的数据:6100 62006300->01100001 00..00 01100010 00..00 01100011 00..00='abc' 这里用六个字节是因为它是NVARCHAR类型 2个字节才能存一个字符

 

第二行数据的读取留给大家实践用.

 

这里的OFFSET TABLE部分我想说的是它这里通过每行偏移量揭示了真正数据行的存储位置                      

1 (0x1) - 140 (0x8c)                 

0 (0x0) - 96 (0x60)

我们可以看到第一行数据是从96的位置开始偏移,为什么是开始呢？因为报头的大小为字节.

经过字节的填充后,第二条从开始偏移;

而且必须注意这里的槽号显示的顺序并不一定就是物理存储的顺序,

也就是说Slot 0 并不一定是物理存储的第一条,即SLOT 0对应的偏移量不一定是最小的.