As BOL says and
depicts : In a clustered index, the leaf nodes contain the data pages of the
underlying table. The root and intermediate level nodes contain index pages
holding index rows. The pages in each level of the index are linked in a
doubly-linked list.
Let’s verify this
using a small example
-- Create a database
Struct.
USE Master
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE name
= 'struct')
DROP DATABASE struct;
GO
CREATE DATABASE struct;
GO
USE Struct
-- Create a clustered table, such that each row in the table
occupies 1 page
CREATE TABLE Clust (col1 int identity primary
key, col2 char(8000))
-- Insert 10 rows in this
table
INSERT INTO Clust VALUES (REPLICATE('a',1))
GO 10
Using DBCC IND let's
see the pages and identify the tree structure. As can be seen the root page of
the B*tree is page 90. I derived this by looking into the IAM page 89 (page type
10). You can used DBCC PAGE (Struct, 1, 89, 1) to get the same information
DBCC IND (Struct,
Clust, 1)
Lets see how the
pages are connected. We will start with the first data page (pagetype = 1) 80.
Using the DBCC PAGE command we can get this infomation. As shown below previous
page (m_prevPage) is 0, whereas next page (m_nextPage) is 93
DBCC TRACEON (3604)
DBCC PAGE (Struct, 1, 80, 0)
/* m_prevPage = (0:0)
m_nextPage = (1:93) */
Now let's read page 93. previous page is 80 and
next is 94 and similarly the doubly linked list goes on. Check yourself
DBCC TRACEON (3604)
DBCC PAGE (Struct, 1, 93, 0)
/* m_prevPage =
(1:80) m_nextPage = (1:94) */
Let's see how a heap differs from this. BOL says
and depicts a heap is a table without a clustered index. The data pages and the
rows within them are not in any specific order and are not linked. The only
logical connection between data pages is the information recorded in the IAM
pages.
-- Create a heap table and
populate 10 rows into it
CREATE TABLE Heap (col1 int identity, col2
char(8000));
GO
INSERT INTO Heap VALUES (REPLICATE('a',10))
GO 10
Using the DBCC IND
command to check the number of pages allocated. This table is not a B*Tree and
thats why there is no index page (pagetype = 2) as for table Clust created
previously. Lets see if the data pages (pagetype = 1) are connected to each
other. The first page is 118
DBCC IND (Struct, Heap, 1)
Using DBCC PAGE we
will extract the header from page 118 as shown below. This is not the complete
header but only the information that we are interested in. Clearly the previous
and the next page links are 0 as also shown pictorially in the diagram above
from BOL
DBCC TRACEON (3604)
DBCC PAGE (Struct, 1, 118, 0)
/* m_prevPage =
(0:0) m_nextPage = (0:0) */
