An Introduction to Clustered and Non-Clustered Index Data Structures

The output will display the data however it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 will display the data page.

I have displayed the data page information for page 26255 only. This is how MS-SQL stores the contents in data pages as such column name with its respective value.  

Record Type = PRIMARY_RECORD                       

EmpId          = 4

EmpName    = dddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

Record Type = PRIMARY_RECORD                       

EmpId           = 6

EmpName     = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Record Type = PRIMARY_RECORD                       

EmpId           = 1

EmpName     = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

This displays the exact data storage in SQL without any index on table. Now, let’s go and create a Unique Clustered Index on EmpID column.

Create a Clustered Index

Now, let us create a unique clustered index on empid column to see how it affects the data that is stored in SQL Server.

CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
ON DummyTable2 (EmpID)
GO

Execute:

Select EmpID From DummyTable2

Here are the results:

Empid

1

3

4

6

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

 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.

Now, let us view the page 26410 and 26412 and see the 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

The output display many columns, but we are only interested in four of them as shown above.

This will display the output of the index page, which has got link to data page (ChildPageID).  EMPID is an index column will contain the starting row of the page.

Now, let us view the page 26410 and see the page details. 

DBCC TRACEON (3604)
GO

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

Here are the results:

Record Type = PRIMARY_RECORD     

EmpId            = 1

EmpName          = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Record Type = PRIMARY_RECORD     

EmpId            = 2

EmpName          = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

Record Type = PRIMARY_RECORD     

EmpId            = 3

EmpName          = cccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

Though I have added disorder records, SQL has displayed the data page in sequence because we have got a clustered index on empid. This is absolutely great!  Adding a clustered index to the table has physically reordered the data pages, putting them in physical order based on the indexed column.

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 |