pf » Dear SQL Server Enterprise Manager Developer

Amen, brother, amen. I don't even use Identity, and I found that to be a crazy depth of burial in the Properties.

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.

I couldn't agree with you more - this has been annoying me recently - if anything its worse in SQL Server 2005 than in 2000...

@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.

I think we're going to have to agree to disagree here. I don't really see how the use of a unique constraint can be used to prevent duplicate data without being applied to every column in the table other than the surrogate key. I would think that would cause more delay in processing than choosing a non-integer key. While I don't like to think too hard about performance at the design stage because it goes against the "never optimize early" rule of thumb, I find that in some cases, use of a non-integer, meaningful key instead of a surrogate is actually faster. That's because when you declare a primary key in MSSQL, you also get a clustered index. If you users wind up doing lookups against a key that is meaningful to them and it happens to be in the clustered index, retrieval times will generally be faster than if it was against a field that had an unclustered index. I'm not suggesting that you never have a use for a surrogate key, although I hesitate to ever use the identity property. It can make sense in some cases where you are dealing with things that are otherwise indistinguishable (cans of beans on a shelf) or which have no good candidate key (forum posts). It just shouldn't be a knee jerk reaction to always use integer keys on every table in the database.

I just about blew the soda i was drinkin out my nose after reading this, I totally need that button!

FWIW from a usability perspective, it's probably better to allow the duplicate data and provide a merge tool to allow users to easily identify and repair their own data integrity issues. Data integrity from the standpoint of the DBA just needs to pass the ACID test, it doesn't necessarily need to reinvent the user.

Something similar happens with deletes -- the best solution is to allow users to undo deletes so they can correct their own mistakes, but the best solution is rarely implemented because it involves more work than the lame alert box the users actually get.

I will say that SQL identity columns have caused major headaches at every company I've worked for, not because of duplicate data, but because they've always resulted in the data migration migraine... okay, let's move the data from... wait... can't do that, okay, then we'll copy the data to... nope, can't do that either... okay, then we'll drop and recreate and ... AAARGH! And for the record, no, enabling identity insert didn't resolve any of those problems for us.

But yeah, if it's a recommended solution, it should be easier to set in their admin tool.

There's nothing wrong with identity keys as long as they are not used as primary keys. A primary key is just one of your candidate keys. The identity is used as surrogate key, to speed up the joins. But you need a proper primary key.