Ok, so basically if I have a table that has no primary key and a
non-clustered index (with unique values checked) on the Id field, I really
don't need the primary key with a clustered index on it?



-----Original Message-----
From: Rogers Michael L
[mailto:mssqldba-ezmlmshield-x52612910.[Email address protected]
Sent: Wednesday, March 30, 2005 9:27 AM
To: LazyDBA Discussion
Subject: RE: Clustered vs Nonclustered

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]



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