PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:

> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,