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:

PagePID

IndexID

PageType

26411

1

10

26410

0

1

26412

1

2

26255

0

1

26408

0

1

26409

0

1

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:

FileID

PageID

ChildPageID

EMPID

1

26412

26410

0

1

26412

26408

4

1

26412

26255

6

1

26412

26409

9

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. 

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

4 Responses to “An Introduction to Clustered and Non-Clustered Index Data Structures”

  1. Where is the SQL code to add the unique clustered index?

    RE: “Now, let’s add a unique clustered index on the empindex column and then view the differences in page 26410.”

  2. Hi Kevin,

    It is on page 5 of this article:

    CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
    ON DummyTable2 (EmpID)
    GO

    Regards, Matt

  3. Great article! I am wondering now what the result will be when adding two clustered indexes on a table for two different rows that you will often query a group of rows on. I’m not sure if I can have multiple clustered indexes on a single table. It seems I would have to have an additional copy of the data to have it physically arranged in two different orders if it’s possible taking up more space and making writes more expensive to the table.

  4. CREATE UNIQUE CLUSTERED INDEX DummyTable1_empindex
    ON DummyTable1 (EmpIndex);
    GO

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |