470,614 Members | 1,491 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

identity, plus pk?

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?

Jul 27 '05 #1
4 1603
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".

Jul 27 '05 #2
Stu
>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

Jul 28 '05 #3
(ne**********@yahoo.com) writes:
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?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 28 '05 #4
Erland Sommarskog wrote:
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 & 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.

Jul 28 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Herv? Bonnard | last post: by
reply views Thread by Ram | last post: by
1 post views Thread by WhiskyRomeo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.