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
)
[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
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 belowdrop 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.