Difference between clustered index and non-clustered index in SQL server

MS SQL ServerMicrosoft TechnologiesDatabase

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.KeyClustered IndexNon-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


raja
Himanshu shriv
Published on 21-Jan-2020 09:32:24
Previous Page Print Page
Next Page  
Advertisements