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 −
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 |