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
Clustered Index | Non Clustered Index |
---|---|
Its created on primary key | It can be created on any key |
Only one per table | Can be used many times per table |
Data retrieval is faster than non-cluster index | Quicker for insert and update operations than a clustered index |
No extra space is required to store logical structure | Extra space is required to store logical structure |
Store data physically according to the order | It don’t impact the order |
CREATE CLUSTERED INDEX <index_name> ON <table_name>(<column_name> ASC/DESC) Example: CREATE CLUSTERED INDEX IX_tblBook_Price ON Books(price ASC) |
CREATE NONCLUSTERED INDEX <index_name> ON <table_name>(<column_name> ASC/DESC) Example : CREATE NONCLUSTERED INDEX IX_tblBook_Name ON Books(name ASC) |
No comments:
Write comments