An Introduction to Clustered and Non-Clustered Index Data Structures

Execute:

Select EmpID From DummyTable1

Here are the results:

EmpID

4

6

1

3

10

2

5

8

9

7

As you may notice above, the data is still in the order we entered it, and not in any particular order. This is because adding the non-clustered index didn’t change how the data was stored and ordered on the data pages.

Now, let’s view the results of the DBCC IND command. In order to find out what happened when the new data was added to the table.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id(‘DummyTable1′)
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID

IndexID

PageType

26408

0

10

26255

0

1

26409

0

1

26412

0

1

26413

0

1

26411

2

10

26410

2

2

Let us execute the page 26410 again and get the index page details.

DBCC TRACEON (3604)
GO

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

SQL Server populates the index column data in order.  The last column (?) is pointed to the row locator.

Here are the results:

Method I

FileID

PageID

EMPID

?

1

26410

1

0x8F66000001000200

1

26410

2

0x2C67000001000000

1

26410

3

0×2967000001000000

1

26410

4

0x8F66000001000000

1

26410

5

0x2C67000001000100

1

26410

6

0x8F66000001000100

1

26410

7

0x2D67000001000000

1

26410

8

0x2C67000001000200

1

26410

9

0×2967000001000200

1

26410

10

0×2967000001000100

As I explained earlier, there are two types of row locations. We have seen Method I.  Now, let’s try Method II with the help of a clustered and non-clustered index in a table. DummyTable1 already has a non-clustered index. Let’s now add a new column to the DummyTabl1 table and add a clustered index on that column. 

Alter Table DummyTable1 Add EmpIndex Int IDENTITY(1,1)
GO

This will link the clustered index key value, instead of the row locator, and be will the combination of fileno, pageno and no of rows in a page.

This adds the Empindex column to DummyTable1. I have used an identity column so that we will not have null values on that column.

You can execute the DBCC ind and DBCC page to check if there any change after the new column is added to the table. If you don’t want to check this yourself, I can tell you that adding the new column did not affect the total number of pages currently allocated to the table by SQL Server.

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

First, we execute the DBCC ind command.  This displays a new set of pages for dummytable1.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id(‘DummyTable1′)
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID

IndexID

PageType

26415

1

10

26414

0

1

26416

1

2

26417

0

1

26418

0

1

26420

2

10

26419

2

2

Pages 26415 and 26420 have page allocation details.  Pages 26414, 26417 and 26418 have data page details.

Now, let’s view pages 26416 and 26419 and see the output.

DBCC TRACEON (3604)
GO

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

Continues…

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 |