An Introduction to Clustered and Non-Clustered Index Data Structures

Now, let’s insert some more rows in our table and view the data and index page storage of our clustered index. 

Insert Into DummyTable2 Values (10, Replicate (‘j’,2000))
GO

Insert Into DummyTable2 Values (2, Replicate (‘b’,2000))
GO

Insert Into DummyTable2 Values (5, Replicate (‘e’,2000))
GO

Insert Into DummyTable2 Values (8, Replicate (‘h’,2000))
GO

Insert Into DummyTable2 Values (9, Replicate (‘i’,2000))
GO

Insert Into DummyTable2 Values (7, Replicate (‘g’,2000))
GO

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id(‘DummyTable2′)

DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

Now, we see few more rows than before. Page 26411 displays the page allocation details, and pages 26408, 26409, 26410 and 26255 display the data page details, as before.

In regard to the new pages, page 26411 displays the page allocation details of an index page and 26412 displays the index page details.

MS-SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index.  It shows the number of index page have been occupied for an index.

Let us see what would be the output for page 26411, that is page type = 10.

DBCC TRACEON (3604)
GO

dbcc page(10, 1, 26411, 3)
GO

Here are the results:

IAM: Single Page Allocations @0x30A5C08E

—————————————–

Slot 0 = (1:26410)

Slot 1 = (1:26412)

Slot 2 = (1:26255)

Slot 3 = (1:26408)

Slot 4 = (1:26409)

Let us view page 26412 to see the index page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26412, 3)
GO

Here are the results:

This helps us to get an idea to decide the need of clustered index. It is really useful to have a clustered index when retrieve many rows of data, ranges of data, and when BETWEEN is used in the WHERE clause. Because, the leaf level of the clustered index is the data. It should be used to save many I/Os. So, it is better to use clustered indexes to solve queries asking for ranges of data, not one row. 

For example, to search for an employee ID (empid between 3 and 9) in a table that has a clustered index on the empid column.

Select EmpID, EmpName From DummyTable1 WHEREEMPID Between 3 And 9

G. Vijayakumar has worked in client server and web application.  He is currently working for Transworld, which is located in Bangalore, India, working on an e-banking product.