Is there any advantage of having a non clustered index same as a clustered index on a table?.
Here is a little background for my question: In our current data warehouse framework (sql server version - 2005), In all of the tables we have one unique non clustered index same as a clustered index. So I was wondering if there is any advantage of doing that.
From my knowledge I think database engine will always pick clustered index over non clustered index (if they both have have column(s) in same order) because clustered index contains data at the leaf level.
whats your thoughts about this?
Answer by Oleg ·
The only possible advantage that I can see could be from the fact that the entries on leaf pages of nonclustered index are not as wide. They only contain index columns while the clustered index' leaf pages are the actual rows of data. Therefore, if you need something like select count(your_column_name) from your_table then scanning the nonclustered index will involve considerably smaller number of data pages. Or if the number of index columns is greater than one and you run any query which does not need data from non-indexed columns then again, nonclustered index scan will be faster.
I would like to add that I do not advocate duplicate indexes. The "Database Maintenance Optimization Strategies for the Database Professional" paper by Brad McGehee is a very good read. Here is the link: http://www.bradmcgehee.com/wp-content/downloads/Brad_McGehee_DatabaseMaintenanceOptimization.pdf
And here is a convincing snippet from Brad's paper:
For many different reasons, it is possible for the same indexes to be recreated using different names. This is resource wasteful and duplicate indexes should be almost always be removed. See the following URL for sample scripts:
Answer by Matt Whitfield ·
The only reason I can see that might be the case, is because the clustered index was not unique and whoever configured it didn't know that you could create a unique clustered index.
Apart from that, your suspicions are correct, it's totally pointless, and will actually harm performance on insert, update and delete operations.