【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

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 ,

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_pages

WHERE IndexLevel >= 0;

/*

PageFID PagePID IndexID IndexLevel PageType

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

5 957 1 0 1 --聚集索引對應的頁面就是資料分頁

5 959 2 0 2 --非聚集索引對應的頁面是索引分頁

*/

go

dbcc traceon(3604);

--我們先來看下非聚集索引在聚集索引表上的儲存

DBCC PAGE('POOFLY',5,959,1)

/*

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

00000000: 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 BYTE

00000000: 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 BYTE

00000000: 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

--再來看看聚集索引鍵是非聚集索引鍵的一部分

CREATE TABLE NC_Overlap (

id int NOT NULL ,

str1 char (5) NOT NULL ,

str2 char (10) NULL

);

GO

--注意這裡的非聚集索引中包含了聚集索引列str2

CREATE UNIQUE CLUSTERED INDEX idxCL_overlap ON NC_Overlap (str2);

CREATE UNIQUE INDEX idxNC_overlap ON NC_Overlap (str1, str2);

GO

--用上面同樣的方法插入資料後,檢視PAGE

/*

PageFID PagePID IndexID IndexLevel PageType

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

5 963 1 0 1

5 965 2 0 2

*/

go

DBCC PAGE('POOFLY',5,965,1)

/*

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

00000000: 16313234 30203337 32202020 20202020 †.1240 372

00000010: 020000†††††††††††††††††††††††††††††††...

。。

*/

結論:

我們可以看到它的索引頁面分佈和上面的情況其實幾乎一樣,唯一的區別就在於STR2的使用

在上面的頁面中,由於STR2只作為聚集索引,所以在索引行中僅僅起到一個書籤的作用

在這個情況的頁面中,STR2不僅作為書籤,還因為它本身是非聚集索引的原因而作為鍵值儲存在上面，

雖然起了個作用,但是它只作為鍵值出現在行中(因為後面還有),並沒有重複出現.但是還是起到了書籤作用.

【SQL Server2005頁面儲存4之--非聚集索引行在葉級別儲存】

相關文章