Clustered and Non clustered index in sql server 2008

Index is a database object used to minimize the number of data comparisons. Index can be created on one or more columns in a table. There are two types of indexes in sql server. They are clustered and Nonclustered Index. Clustered index is an index that stores the actual data but Non Clustered index is a pointer to the data.

Clustered Index:

Clustered Index can be defined as the way on which the records of the table are physically stored. In clustered index, while inserting data itself it is sorted in heap order. Only one clustered index can be created per table.
A clustered index will be created while a primary key is created on the table.
We can create an index while creating a table and also we can create index separately.

Syntax for creating Index:

--Creating index while creating table
create table Table_Name
Column_Name datatype constraint Constraint_Name Primary Key


create table Table_Name
Column_Name datatype

--Creating Index on already existing table
create clustered index Index_Name on Table_Name(Column_Name)

To check how the index minimizes the number of data comparisons. We have created one sample table.

;with cte as
select 1 Sno,10 Points
union all
select Sno+1 Sno, Points +10 Points from cte where Sno< 30000
select * into Index_Table from cte option (MAXRECURSION 30000)
select Sno, Points from Index_Table

Here Index_Table table having two columns Sno and Points. To check the number of comparison performed while run the select query with Query Execution Plan to find the Points where Sno is equal to 9555.
select points from Index_Table where Sno=9555

Execution Plan 1

We can see that,
Estimated Subtree Cost = 0.0829487
Estimated Number of rows = 2279
This represent that the system compares 2279 rows to find the actual data.
Now I created a clustered index on Sno to reduce the Subtree cost and to minimize the number of comparisons.

create clustered index Clx_Idx_SNo on Index_Table(Sno)

select points from Index_Table where Sno=9555

Execution Plan 2

Now we can find that Estimated Subtree Cost reduced from 0.08 to 0.003 and the system compares the single data to find the actual data.
We can drop the index in a table by using the syntax below
drop index Index_Table.Clx_Idx_SNo

NonClustered Index:

A NonClustered index is just a pointer to the data which points to the clustered index where the data is sorted. In NonClustered index the data are stored in the form of logical order. We can create NonClustered index on 999 columns in a table.
For Example Non Clustered Index is an index of the book and clustered index is an actual page of the book.

create Nonclustered index NClx_Idx_SNo on Index_Table(Sno)

Execution Plan 3

The indexes are built to provide faster data access. We can create index by the combination of two or more columns. Without having an index SQL Server need to read all the data to find the rows that satisfy the query.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: