Difference between Cluster and Non-cluster index in SQL

sql indexing clustered-index non-clustered-index
AjmeraInfo picture AjmeraInfo · Mar 3, 2010 · Viewed 13.9k times · Source

Just for knowledge in interview question, and my knowledge.

SQL - Difference between Cluster and Non-cluster index?

Answer

kemiller2002 picture kemiller2002 · Mar 3, 2010

A link describing the two.

http://www.mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm

http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

The difference is in the physical order of the records in the table relative to the index. A clustered index is physically ordered that way in the table.

Related questions

Best way to change clustered index (PK) in SQL 2005

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_…

Read More
Does SQL Server creates Non clustered index by default

Ya, it is a duplicate of this. But I just needs a clarification on this article by Pinal Dave, which says the following: Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index In this case …

Read More
How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More