identity, plus pk?

[newtophp2000@yahoo.com]

We have a table that has an identity field along with 5 other domain
fields. The identity field is not declared as a primary key. The
table has 3.5 million records.

A consultant was hired recently to provide insight. His major
recommendation: modify the table to make the identity field a primary
key (i.e., alter table add constraint...)

Is that sound advice? Is it OK to have a table with identity but no
primary keys? What would be the impact on performance?

[--CELKO--]

Re: identity, plus pk?

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.

Actually, IDENTITY cannot be a relational key by definition. I would
drop that column and construct a proper key from the other columns. I
am willing to bet that you will fidn that you have a lot of invalid and
redudant data in this "non-table".

[Stu]

Re: identity, plus pk?

>From a strict performance perspective, the presence or absence of keys
has no real effect, especially given the fact that you've managed to
collect 3.5 million records of data without relational constraints.

Your consultant probably meant to encourage you to build a unique
clustered index, which is built by default when you add a primary key
constraint. However, they are not the same; a clustered unique index
can exist without a primary key, and a primary key need not be
clustered (it must, however, be unique). Check the Books OnLine for
clustered indexes, or visit www.sql-server-performance.com for more
help with indexes.

Stu

[Erland Sommarskog]

Re: identity, plus pk?

(newtophp2000@y ahoo.com) writes:[color=blue]
> We have a table that has an identity field along with 5 other domain
> fields. The identity field is not declared as a primary key. The
> table has 3.5 million records.
>
> A consultant was hired recently to provide insight. His major
> recommendation: modify the table to make the identity field a primary
> key (i.e., alter table add constraint...)
>
> Is that sound advice? Is it OK to have a table with identity but no
> primary keys? What would be the impact on performance?[/color]

If the table does have a primary key, defining one is a very good idea.
If the identity column is the only column that is unique in the table,
then there is not much choice.

It's difficult to say what the performance might be, since I don't know what
indexes there are on the table today. But if there are none at all, then
adding an index on the identity column is likely to improve things.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[newtophp2000@yahoo.com]

Re: identity, plus pk?

Erland Sommarskog wrote:[color=blue]
> If the table does have a primary key, defining one is a very good idea.
> If the identity column is the only column that is unique in the table,
> then there is not much choice.
>
> It's difficult to say what the performance might be, since I don't know what
> indexes there are on the table today. But if there are none at all, then
> adding an index on the identity column is likely to improve things.
>[/color]

Erland & Stu,

Thank you very much for your input; it was right on the money. The
table as it stands does not have any indexes and the identity field
provides the uniqueness criteria for us. The performance is quite
good. We will do some tests to see the impact of a primary
key/clustered index on overall performance before moving forward.