非聚集索引行在叶级别存储的时候也分在堆上、在聚集索引的表上.
一：堆上的非聚集索引在叶级别的存储
/*----------------------------------------------------------------------
*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_pages
WHERE 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 1
5 953 0 0 1
5 954 0 0 1
5 955 0 0 1
5 956 0 0 1
*/
go
我们从结果可以看到由于数据行的量比较少只有行 所以我们只需要一个索引页来存储.
IndexID为 PageType为 说明它是一个存储非聚集索引的索引分页,IndexLevel为 说明是叶级别的
--来看看这个页面的存储特点:
dbcc traceon(3604);
dbcc page(poofly,5,54,1)
/*
Slot 0, Offset 0x60, Length 14, DumpStyle BYTE
00000000: 06313234 30203400 00000500 0000††††††.1240 4.......
Slot 1, Offset 0x6e, Length 14, DumpStyle BYTE
00000000: 06313234 31203400 00000500 0100††††††.1241 4.......
....
Slot 59, Offset 0x39a, Length 14, DumpStyle BYTE
00000000: 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
验证完毕,确实就如索引行记录的那样,利用书签,可以精确地找到我们的值在数据页的位置。
结论:
这里需要注意的是索引行的不包含状态位B.书签中的行号是从开始的.
非聚集索引在叶级别的索引行是存储了了它的非聚集键值和一个书签(指向包含非聚集值数据页的一个指针)
二：在含聚集索引的表上非聚集索引在叶级别的存储
这里也分种情况：一种是非聚集索引不包含聚集索引列,还有一种是非聚集索引列中包含了聚集索引列
--先来看第一种非聚集索引不包含聚集索引列
--建表(表源技术内幕系列)
CREATE TABLE NC_Nodupes (
id int NOT NULL ,
str1 char (5) NOT NULL ,