Pete Freitag Pete Freitag

Dear SQL Server Enterprise Manager Developer

Published on July 31, 2008
By Pete Freitag
databases

Please add a button to make a column an identity.

You have a button to set as primary key, why do we have to scroll down through 3/4 of the properties, click the plus under "Identity", and then choose "Yes" for such a common operation?

Thank You



sql sql server microsoft

Dear SQL Server Enterprise Manager Developer was first published on July 31, 2008.

If you like reading about sql, sql server, or microsoft then you might also like:

Discuss / Follow me on Twitter ↯

Comments

Amen, brother, amen. I don't even use Identity, and I found that to be a crazy depth of burial in the Properties.
by jfish on 07/31/2008 at 1:38:11 PM UTC
I think they should bury it deeper to discourage it's use. It's super simple in code:

create table myTable
( myId int not null IDENTITY(1,1) PRIMARY KEY)

The big problem with them is that they often allow for the entry of duplicate data that differs only by the id value. Things seem to work out much better if you can find a better candidate key in your data, even if it's a complex key.
by Mike Rankin on 07/31/2008 at 4:41:41 PM UTC
I couldn't agree with you more - this has been annoying me recently - if anything its worse in SQL Server 2005 than in 2000...
by Dan Lancelot on 07/31/2008 at 5:00:07 PM UTC
@Mike, why not just use a uniqueness constraint to avoid the duplicate data? I don't think it's valid to discourage their use. Using non integer primary keys can be really really slow in large tables.
by Pete Freitag on 07/31/2008 at 9:49:59 PM UTC
@Mike,

I think the use of the primary key can be completely separate from other constraints you may need on a table. As Pete points out, you can still set a unique constraint on a set of columns without making that complex key into the primary key.

I often do that, especially in child tables. Example: a folder table and a file table, where the file name has to be unique per folder, but not across folders, so maybe like this:

PK: fileID
FK: folderID
unique constraint: folderID, fileName

At that point, whether my keys are integer or UID or whatever, they're independent of the uniqueness. I think that the cross-table lookups are faster when the RDBMS doesn't have to compare complex keys.
by jfish on 08/01/2008 at 5:39:21 AM UTC
@Mike if your uniqueness constraint has to span all columns then you don't have a meaningful key already in your table. This is often the case, rarely do I have a column that is unique aside from the primary key. If such a column exists then I think it is wise to consider alternate primary keys.

It is a design decision, and with all design decisions there are trade offs.
by Pete Freitag on 08/01/2008 at 9:12:14 AM UTC
I just about blew the soda i was drinkin out my nose after reading this, I totally need that button!
by Chris Carter on 08/05/2008 at 8:54:20 AM UTC