There are situations where you will get a performance improvement with a
redundant clustered and nonclustered indexes but it is entirely dependent on
A non-clustered index on the PK will cover queries such as WHERE EXISTS and
those that join on the PK but do not retrieve data from that table. This
will be very fast because of the narrow index. The clustered index on the
PK may help queries which join and retrieve data from the table.
However, you need to balance the SELECT performance improvements with the
INSERT performance degradation.
Hope this helps.
SQL FAQ links (courtesy Neil Pike):
(faqxxx.zip in lib 7)
Quote:> I have a table created by my predecessor.
> He has the primary key set up as non-clustered and then an index set up
on the primary key field and set as clustered. Would it not be better just
to set the primary key up as clustered?
> This scenario is the same in a number of tables, will it affect
> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index