There is also a side benefit of a PK. Many programs and tools can read the
metadata and use PK/foreign key declarations to understand the structure of
the data. It can simplify maintenance.
Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers
-----Original Message-----
From: Rogers Michael L
[mailto:mssqldba-ezmlmshield-x96349174.[Email address protected]
Sent: Wednesday, March 30, 2005 10:06 AM
To: LazyDBA Discussion
Subject: RE: Clustered vs Nonclustered
'Need' is subjective. If your current key values have no 'null' values,
then you can make that key the primary key. It will ensure uniqueness and
'not null' on all key values. If your existing index is the primary method
of access (I'd say it is since you said you only have one key), then you can
make it your clustered index and it should optimize performance.
Whatever the case, the two should be considered separately. You can make
the key primary, you can cluster it, or you can do both (or none, as you
have it now).
My gut says I'd make your current index primary and cluster it. But since I
don't know your data I can't say for sure. However clustering it seems like
a slam dunk to me. You can always uncluster it later if you come up with a
better index to cluster.
Michael
-----Original Message-----
From: KPS Info
[mailto:mssqldba-ezmlmshield-x84030443.[Email address protected]
Sent: Wednesday, March 30, 2005 8:41 AM
To: LazyDBA Discussion
Subject: RE: Clustered vs Nonclustered
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]
---------------------------------------------------------------------
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