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?
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
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.
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.
