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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.