Clustered and Non clustered index in sql server 2008

Posted Date: 05-Sep-2012	Category: SQL Server
Author: kirthiga	Member Level: Gold	Points: 20

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
)

[OR]

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

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)

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.

Post Comment:

Notify me by email when others post comments to this article.

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:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.

Online Members

Clustered and Non clustered index in sql server 2008

Clustered Index:

Syntax for creating Index:

NonClustered Index:

Post Comment:

Email subscription