Primary key vs Clustered Key

Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that is
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
Jamie
That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than have
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey is
constrained by that primary key, is there truly any use for it?
Regards,
Jamie
"MC" wrote:

> What do you mean?
> When creating PK you by default get clustered index on same columns. In some
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>
|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with an
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck at
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustered
index as unique as opposed to a primary key that references the cluster allow
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is completely
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a clustered
> index as unique as opposed to a primary key that references the cluster allow
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working with
> a view that must be faster and more efficient. If I can eliminate a primary
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being more
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. With
the primary key, every row could have a clustered field as null.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I see.
The primary key will always be unique, will it not? A unique cluster can
be the only unique key in a table. There can't be both a primary key and a
unique cluster. So not having a primary key would actually result in one
less column. It would mean not having many records that could contain all
null values for that cluster. I think it answers the question. The unique
cluster is a better choice than a primary key if there are to be no foreign
keys for the table. I hope I understand it correctly.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Narrow indexes have nothing to do with whether an index is a PRIMARY
> KEY or not.
> Maybe what you really mean is that you want to drop a *column* or
> remove a *column* from an index. But that has nothing to do with having
> a primary key or not - obviously you could just as easily put the
> PRIMARY KEY constraint on the other unique column(s) you have.
> Remember that a PRIMARY KEY is automatically indexed. There is no need
> to create another index.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
sql