Difference between Clustered and Non-Clustered Indexes

Most of the performance issues are being faced is about how indexes are implemented. Although indexing is very benefical, it can cause performance problems if it is not well structured. In this post i am going to explain the difference between Clustered and Non-Clustered, and also on which condition we should prevent using indexing.

Following explains the methods we are going to use ;

* Lists all of a table's data and index pages
DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

* Setting via trace flag 3604, It allows to instruct the engine to send output to the console
DBCC TRACEON(3604)

* DBCC PAGE statement lists the page header, data rows, and row offset table for any data page in a database.
DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache]


Lets have a look how they stored by SQL;


1) Non indexed Table
Tables do not have a default index.

. Create the test table

Create Table TestTable
(
Id Int,
Name Varchar(255)
)

. Insert test data

Insert Into TestTable Values (3, 'Name3')
Insert Into TestTable Values (1, 'Name1')
Insert Into TestTable Values (2, 'Name2')

. Query the table

Select * From TestTable

. See the result

Id Name
-------------- ------------------
3 Name3
1 Name1
1 Name2

As there is no index, data was returned as the same order as they were inserted.

. See the structure of the data
Declare @dbID Int, @TableID Int
Set @dbID = db_id()
Set @TableID = object_id('TestTable')
DBCC ind(@dbID, @TableID, -1)

Here is the result



The columns mean:

* PageFID - the file ID of the page
* PagePID - the page number in the file
* IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)
* IAMPID - the page number in the file of the IAM page that maps this page
* ObjectID - the ID of the object this page is part of
* IndexID - the ID of the index this page is part of
* PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of
* PartitionID - the internal ID of the partition this page is part of
* iam_chain_type - see IAM chains and allocation units in SQL Server 2005
* PageType - the page type. Some common ones are:
1 - data page
2 - index page
3 and 4 - text pages
8 - GAM page
9 - SGAM page
10 - IAM page
11 - PFS page
* IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))
* NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index
* PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index


But we are only interested with PagePID, IndexID and PageType