Advantage of having nonclustered index same as clustered index on a table

Question

question

pvm_25 asked May 6, '10

Advantage of having nonclustered index same as clustered index on a table

Hello guys,

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?

sql-server-2005 sql indexing indexes

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-06T11:59:31Z

Matt Whitfield answered May 6, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-05-06T12:03:12Z

Oleg answered May 6, '10

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:

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

Oleg

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question