An Introduction to Clustered and Non-Clustered Index Data Structures

Here are the results:

FileID

PageID

ChildPageID

EMPID

1

26416

26414

0

1

26416

26417

5

1

26416

26418

9

This displays the output of the clustered index page, which has got a link to data page (ChildPageID).  EMPID is an index column that contains the starting row of the page.

DBCC TRACEON (3604)
GO

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

Here are the results:

Method II

FileID

PageID

EMPID

EMPIndex

1

26419

1

1

1

26419

2

2

1

26419

3

3

1

26419

4

4

1

26419

5

5

1

26419

6

6

1

26419

7

7

1

26419

8

8

1

26419

9

9

1

26419

10

10

It is interesting to see the differences now. There is a difference between Method I and Method IIMethod II is now linked to a clustered index key.

The main difference between Method I and Method II is the link to a row in a data page.

Part II: Clustered Index

Creating a Table

To better explain how SQL Server creates clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. You can use the same sample database as before.

Create Table DummyTable2

(
    EmpId Int,
    EmpName Varchar(8000)
)

As in the previous example, when you first create a new table, there is no index created by default, and a heap is created. As before, we can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.

Now let’s add a few records in this table using this script:

Insert Into DummyTable2 Values (4, Replicate (‘d’,2000))
GO

Insert Into DummyTable2 Values (6, Replicate (‘f’,2000))
GO

Insert Into DummyTable2 Values (1, Replicate (‘a’,2000))
GO

Insert Into DummyTable2 Values (3, Replicate (‘c’,2000))
GO

Now, let’s view the contents of the table by executing the following command in Query Analyzer for our new table.

Select EmpID From DummyTable2
GO

Empid

4

6

1

3

As you would expect, the data we inserted has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

DBCC ind(dbid, tabid, -1) – It is an undocumented command. 

DBCC TRACEON (3604)
GO

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

This script will display many columns, but we are only interested in three of them, as shown below.

Here are the results:

PagePID

IndexID

PageType

26408

0

10

26255

0

1

26409

0

1

Here’s what the information displayed means:

PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.

IndexID is the type of index,

Where:

0 – Datapage

1 – Clustered Index

2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index)

PageType tells you what kind of data is stored in each database

Where:

10 – IAM (Index Allocation MAP)

1 – Datapage

2 – Index page

Now, let us execute DBCC PAGE command.

DBCC page(dbid, fileno, pageno, option)

Where:

dbid = database id.

Fileno = fileno of the page.  Usually it will be 1, unless we use more than one file for a database.

Pageno = we can take the output of the dbcc ind page no.

Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data.  You can try yourself for the other options.

Run this script to execute the command:

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26408, 3)
GO

The output will be page allocation details.

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26255, 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 |