Menu
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?
834
Again, if you are constantly returning data in specific order then create a non-clustered index to speed up the performance. It will also reduce the memory footprint as you will not be required to perform an additional sorting.
If you want to select only the index value that is used to create and index, non-clustered indexes are faster.
Non-Clustered Indexes A non-clustered index doesn't sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another.
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
91
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
