Difference Between Clustered Index and Non Clustered Index :
In my previous articles i have already explained the different kind of performance tuning techniques as well as Different indexing techniques.In this article i would like to give you Difference between Clustered Index and Non Clustered Index in depth.In so many interviews the main question asked about the difference between clustered index and Non clustered Index . In this article i would like to explain Difference between Clustered index and Non Clustered index with real life industry examples.
Clustered Index with Examples :
In this section i would like to explain the concept of Clustered indexes with real life industry examples.I will not go in to very deep but i will give some simple bullet points of Clustered index with real life examples so that user will understand the concept.
Definition :
Clustered index is index where the records of specified tables are physically reordered to speed up the fetching of the data from the table.
Bullet-points :
1.Clustered indexes are used order the records in the specified table to retrieve that records fast.
2.The Clustered indexes are stored in B-tree structure in SQL server.
3.One table can have only one clustered index as it is created at the time of table creation.
4.The Primary key constraint automatically create the clustered index by default.
5.The clustered index id always has index id as 0 (Zero).
6.The primary key constraint are enforced with non-clustered indexes but user should specify the index type as non clustered at the time of creating table.
7.If the table does not have the clustered index then that table is specified as Heap structure.
8.The Clustered indexes are faster to read as compare to non clustered indexes as the data is stored physically in sorting order in clustered indexes.
9.In Clustered index the rows can be ordered based on the key defined in index.
10.There is only one clustered index per page but user can create composite clustered index by using some ways.
11.The leaf node of clustered index consist of data pages.
Real life Industry Example :
If user wants to create the clustered index on Employee table for Employee_Id column. Kindly use following query for the same,
Create table Employee
(
Employee_Id INT Primary Key,
Employee_name varchar(20) Not null,
Employee_Position varchar(20) Not Null);
The above table is created with Employee_Id as primary key.The clustered index has been created on Employee_Id column of Employee table.User needs to check the index created on the same table.User needs to use the SP_helpindex procedure.
SP_helpindex Employee;
The above query will give the index name created internally with index description.
Real life Industry Example with Custom create indexes :
If the table does not have clustered index then user can create custom clustered index with using keyword as Clustered,
Syntax :
Create clustered index Index_Name
On Table_Name(Column1,column2…);
Lets take example of Employee table and user needs to create the clustered index on Employee_Id column then following query is useful,
Query :
Create clustered index CI_Employee_Id
On Employee(Employee_Id);
The above query will create the index named CI_Employee_Id which is clustered index.
Non clustered Index with Example :
In this section i would like to explain about Non clustered index with real life examples.
Definition :
A non-clustered index is nothing but the special type of index which contains the logical order of index which will not match the physical stored order of rows on disk.
Bullet-points :
1.A non-clustered indexes contains the logical order of index which does not match the physical stored order of rows on disk.
2.User can create 249 non-clustered indexes before SQL server 2008 and 999 Non-clustered indexes with SQL server 2008 and above versions.
3.The Non Clustered indexes have index id greater than zero.
4.The Unique key constraint created a non-clustered index by default.
5.The Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key.
6.There should or should not be non-clustered index for table.
7.The leaf nodes of a Non-clustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row.
8.The Non clustered indexes are slower than clustered indexes for Select operation but for insert and update operation
9.The Non Clustered indexes the rows can be ordered in logical way rather than physical way.
Difference Between Clustered Index and Non Clustered Index in Tabular Format :
| Clustered Index | Non- Clustered Index |
| The Clustered Indexes are indexes which will sort the data physically | Non- Clustered indexes does not sort the data physically. These are logical indexes. |
| Only one clustered index created for one column | User can create up to 999 non- clustered indexes |
| These indexes are faster to read than non- clustered indexes | Non- clustered indexes are slower in read operation as compare to clustered indexes. |
| The select operations are fast in clustered indexes | Insert and update operations are fast in non-clustered indexes |
| Clustered indexes will only sort the table in specific order so it will not consume the physical space | Non-clustered indexes are physically stored indexes which works in logical way. |
| Clustered index contains data at the leaf node | Non- clustered indexes do not contain data at leaf node. |
These are some important points with examples for Difference between clustered index and non clustered index.If you like this article on difference between clustered index and non clustered index or if you have concerns with the same kindly comment in to comments section.