Difference between clustered index and non-clustered index in SQL server
Index is a lookup table associated with actual table or view that is used by the database to improve the data retrieval performance timing. In index , keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Index gets automatically created if primary key and unique constraint is defined on the table. There are two types of index −
- Clustered Index - Table is created with primary key constraints then database engine automatically create clustered index . In this data sort or store in the table or view based on their key and values.
- Non-Clustered Index - Table is created with UNIQUE constraints then database engine automatically create non-clustered index . A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
| Sr. No. | Key | Clustered Index | Non-Clustered Index |
|---|
1 | Basic | Its created on primary key | It can be created on any key |
2 | Ordering | Store data physically according to the order | It don’t impact the order |
3 | Number of index | Only one clustered index can be there in a table | There can be any number of non-clustered indexes in a table |
4 | Space | No extra space is required to store logical structure | Extra space is required to store logical structure |
5 | Performance | Data retrieval is faster than non-cluster index | Data update is faster than clustered index |
Published on 21-Jan-2020 14:02:24
