In general a clustered index should be the index that is most often used
to access the data. If you usually look up data with a location number,
then your clustered index should be on the location number. In many
cases, your primary key is the one that is clustered, but not always.
The primary key is not necessarily required, though it's useful for
ensuring that a record is unique. I have tables that do not have a
unique identifier.... In some cases I add an identity field so that I
can make each record unique. Sometimes I don't care. If you are sure
that your ID field is unique in the table, you can make the index that
uses it the primary key. But you need to know for sure.

Michael Rogers

-----Original Message-----
From: KPS Info
[mailto:mssqldba-ezmlmshield-x79834472.[Email address protected]
Sent: Wednesday, March 30, 2005 8:09 AM
To: LazyDBA Discussion
Subject: Clustered vs Nonclustered

Hi all - basic question here I think.

If I have a table that has no Primary Key defined but has a non
clustered index on the Id field, will it perform better or worse if it
had a Primary Key field with a clustered index on the Id field? Say the
table had a million rows in it.

I'm looking for the best performance. I always understood that it was
better to have a PK on a table. Thats like Rule 101 of DBA class right?

Thanks!



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page