Very Computer

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

* Sent via Developersdex.com http://www.developersdex.com *
The Web Developers Index

Rena,

You are correct in your assumptions.
Having two the same index on a table is redundant and can hurt performance for
modification operations.

Then whether you want to "waste" the one and only clustered index on the primary key,
is another issue...
--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

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?

Quote:

> This scenario is the same in a number of tables, will it affect performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index

There are situations where you will get a performance improvement with a
redundant clustered and nonclustered indexes but it is entirely dependent on
your queries.

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):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------

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?

Quote:

> This scenario is the same in a number of tables, will it affect
performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index

You're correct about only needing one of these indexes. You'll have to
determine, for each table, whether you should keep the clustered or
non-clustered.

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?

Quote:

> This scenario is the same in a number of tables, will it affect
performance?

> * Sent via Developersdex.com http://www.developersdex.com *
> The Web Developers Index