A Table can have two type of index that is Clustered Index and Non-Clustered Index but both job is to store data in sorted order and retrieve a data faster when perform a query.
Clustered Index
The Clustered Index determines how data is stored in physically on the table in the sorted order. Advantage of using index is to store data in table in sorted order even insertion in the table is not sorted. Default sorting of data is ascending.
A table can have only one clustered index and this is usually created by default on the primary key. A clustered index can be created for another column also if primary key is not exists on the table.
A table can have only one clustered index and this is usually created by default on the primary key. A clustered index can be created for another column also if primary key is not exists on the table.
For an Example - The primary key on EmpId column will create a clustered index for the EmpId column. A table can have only one clustered index on it.
SQL Server stores data in the table according a clustered index. It helps to make SELECT query faster. A query with index is always faster than a query without index.
getdate()
SELECT * FROM EMPLOYEE where EmpId = 150
getdate()
Insert some dummy data into Employee Table, Let's say 10,000 records to measure the above query execution time without index and with index on EmpId.
If no any index is added then each row will be compared to find the EmpId 150. If Clustered index is created on the table then it will give records faster from physically stored data using a search technique Binary Search Tree.
Clustered Index is slightly faster than non-clustered index.
Clustered Index is slightly faster than non-clustered index.
Non-Clustered Index
When the Non-clustered index created then It takes extra physical storage of the data in sorted order of non-clustered index column with a row pointer which points to row of actual storage data in the table. When perform a query on non-clustered index column then it do extra lookup for actual data which is stored in the table with the help of row pointer.
Non-Clustered are generally used when we want to have more than one index other than primary key constraint.
We can create a single non-clustered index for multiple columns and all columns will be sorted as specified in the index. We call it Composite Clustered Index.
The 249 Non-clustered Index can be created per table in SQL Server 2005 where SQL server 2008 can have 999 non-clustered Index per table.
Non-Clustered are generally used when we want to have more than one index other than primary key constraint.
We can create a single non-clustered index for multiple columns and all columns will be sorted as specified in the index. We call it Composite Clustered Index.
The 249 Non-clustered Index can be created per table in SQL Server 2005 where SQL server 2008 can have 999 non-clustered Index per table.
On which Column we have to apply the non-Clustered Index -
- The column used multiple time in the search criteria across.
- The column most of the time used to join other tables.
- The column used as Foreign Key most of the time.
- The column used in Join, Where, and Order By clauses in most cases
4 Main Differences in Clustered Index and Non-Clustered Index
1. Only one clustered Index can be created per table where non clustered index can be created per table more than one.
2. Clustered Index is by default unique key Index where non-clustered index can be non unique too.
3. Clustered Index are slightly faster than Non-Clustered Index.
4. Clustered Index stored sorted data with in the table itself where non-clustered index required extra disk storage to sort the data.
