CODEWITHSUNDEEP
sql-serverindexingsql-server-2005
Kevin Berridge
Kevin Berridge

Reputation: 6303

Non-Clustered Index on a Clustered Index column improves performance?

In SQL Server 2005, the query analyzer has told me many times to create a non-clustered index on a primary ID column of a table which already has a clustered index. After following this recommendation, the query execution plan reports that the query should be faster.

Why would a Non-Clustered index on the same column (with the same sort order) be faster than a Clustered index?

Upvotes: 10

Views: 7635

Answers (3)

SQLMenace
SQLMenace

Reputation: 135181

A clustered index has all the data for the table while a non clustered index only has the column + the location of the clustered index or the row if it is on a heap (a table without a clustered index). So if you do a count(column) and that column is indexed with a non clustered index SQL server only has to scan the non clustered index which is faster than the clustered index because more will fit on 8K pages

Upvotes: 16

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

A clustered index will generally be faster, but you can only have 1 clustered index. So if the table already has a clustered index on a different column, then a non-clustered index is the best you can do.

Upvotes: -1

BlaM
BlaM

Reputation: 28898

I'd guess it would be faster in cases where you don't need the full row data, for example if you're just checking if a row with a given ID does exist. Then a clustered index would be rather huge while a small "one column" index would be much slimmer.

Upvotes: 2

Related Questions