【SQL Server2005页面存储4之-非聚集索引行在叶级别存储】解决办法

/*----------------------------------*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 NC_Heap_Nodupes (  id int NOT NULL ,  str1 char (5) NOT NULL ,  str2 char (600) NULL ); GO--在str1上建立非聚集索引CREATE UNIQUE INDEX idxNC_heap ON NC_Heap_Nodupes (str1); GO--插入数据DECLARE @i int;SET @i = 1240;WHILE @i < 1300 BEGIN  INSERT INTO NC_Heap_Nodupes    SELECT @i, cast(@i AS char), cast(@i AS char);  SET @i = @i + 1; END; GO--执行DBCC将结果插入表并显示相关页号TRUNCATE TABLE sp_table_pages;INSERT INTO sp_table_pages    EXEC ('dbcc ind ( poofly, NC_Heap_Nodupes, -1)'  );SELECT PageFID, PagePID, IndexID, IndexLevel, PageType FROM sp_table_pagesWHERE IndexLevel >= 0;/*PageFID PagePID     IndexID IndexLevel PageType------- ----------- ------- ---------- --------5       52          0       0          1           --pagetype 1 数据分页2索引分页5       54          2       0          2           --IndexID 0为堆 1为聚集索引2-250为非聚集索引    5       952         0       0          15       953         0       0          15       954         0       0          15       955         0       0          15       956         0       0          1*/go    我们从结果可以看到由于数据行的量比较少只有行 所以我们只需要一个索引页来存储.    IndexID为 PageType为 说明它是一个存储非聚集索引的索引分页,IndexLevel为 说明是叶级别的--来看看这个页面的存储特点:dbcc traceon(3604);dbcc page(poofly,5,54,1)/*Slot 0, Offset 0x60, Length 14, DumpStyle BYTE00000000:   06313234 30203400 00000500 0000??????.1240 4.......           Slot 1, Offset 0x6e, Length 14, DumpStyle BYTE00000000:   06313234 31203400 00000500 0100??????.1241 4.......      ....Slot 59, Offset 0x39a, Length 14, DumpStyle BYTE00000000:   06313239 3920bc03 00000500 0400??????.1299 ........           */--先来看第一条索引行Slot 0    06->状态位A(没有状态位B在索引行中)    313234 3020->=='1240 ' 它是一个非聚集值    3400 0000->Ox0034=52 这个书签的分页指针指向的页号 52    0500->书签的文件ID    0000->书签的槽号(行号,从开始)--第二条和第一条的区别    313234 3120->=='1241 '    3400 00000500 0100->这个字节其实就是一个完整的书签 我们发现这个和上面记录的区别就是最后的书签的槽号(行号)不同,因为是条记录嘛--最后一条索引记录    313239 3920->=='1299 ' 我们发现它的值就是我们表内的最后一条记录的非聚集值    bc03 0000->Ox03bc==956 我们可以看这个页号,这个存储我们表内最后一条记录的页号不就是么?    0500->文件号     0400->说明我们的这条记录在页里面是第五条记录     --我们来验证下上面的假设dbcc page(poofly,5,956,1)/*Slot 4, Offset 0xa00, Length 616, DumpStyle BYTE  ...00000000:   10006502 13050000 31323939 20313239 ?.e.....1299 129         00000010:   39202020 20202020 20202020 20202020 ?                        00000020:   20202020 20202020 20202020 20202020 ?、。。。。。00000250:   20202020 20202020 20202020 20202020 ?                        00000260:   20202020 20030000 ?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??            */    我们拿出第四条来看看我们的str1值的大小吧?    注意看第个字节-第字节 3132303920=='1299 ',而它对应的行数 正是第五行 Slot 4    验证完毕,确实就如索引行记录的那样,利用书签,可以精确地找到我们的值在数据页的位置。    

--建表(表源技术内幕系列)CREATE TABLE NC_Nodupes (  id int NOT NULL ,  str1 char (5) NOT NULL ,  str2 varchar (10) NULL ); GO--在str2上建立唯一聚集索引,在str1上建立唯一非聚集索引CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_Nodupes (str2);CREATE UNIQUE INDEX idxNC ON NC_Nodupes (str1); GO--插入数据DECLARE @i int;SET @i = 1240;WHILE @i < 1300 BEGIN  INSERT INTO NC_Nodupes    SELECT @i, cast(@i AS char),        cast(cast(@i * rand() AS int) as char);   --打乱了str1的排序  SET @i = @i + 1; END; GO--检查页面分布TRUNCATE TABLE sp_table_pages;INSERT INTO sp_table_pages    EXEC ('dbcc ind (poofly, NC_Nodupes, -1)');SELECT PageFID, PagePID, IndexID, IndexLevel, PageType FROM sp_table_pagesWHERE IndexLevel >= 0;/*PageFID PagePID     IndexID IndexLevel PageType------- ----------- ------- ---------- --------5       957         1       0          1            --聚集索引对应的页面就是数据分页5       959         2       0          2            --非聚集索引对应的页面是索引分页*/godbcc traceon(3604);--我们先来看下非聚集索引在聚集索引表上的存储DBCC PAGE('POOFLY',5,959,1)/*Slot 0, Offset 0x60, Length 23, DumpStyle BYTE00000000:   36313234 30200200 00010017 00313531 ?1240 .......151         00000010:   20202020 202020?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ?   Slot 1, Offset 0x77, Length 23, DumpStyle BYTE00000000:   36313234 31200200 00010017 00313930 ?1241 .......190         00000010:   20202020 202020?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ?       ....Slot 57, Offset 0x57f, Length 23, DumpStyle BYTE00000000:   36313239 39200200 00010017 00363533 ?1299 .......653         00000010:   20202020 202020?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ?   */--先来看第一条索引行    36->状态位A:00110110 1-3位=3表示索引记录,第位表示存在NULL位图 第五位表示存在变长列    313234 3020->'1240 '    0200->表的字段数目 2行    00->00000000NULL位图 最后四位为表示记录没有一个COL为NULL    0100->00000001 =1 变长列的数目    17 00->00000000 00010111=16+7=23 表示第一列变长列的结束位置因为只有一列变长列  所以也就是列长了    313531 20202020 202020->=='151       '这里的这个值是聚集值str2