Translate to Tamil

Earn Money


Best Offers

Amazon Best Offers

Offers

Best Online Offers

Amazon Offers

Earn Money Short URL

Sunday, 1 November 2020

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



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

Contact form

Name

Email *

Message *

Infolinks

© 2014