GilaMonster (3/1/2010)
I don't have a problem with using identities. What I do have a problem with is using identities as the only unique identifier of the entire table.When the primary key is an identity (or a GUID) and there is no other unique column or set of columns in the table, then it's easy to get into the situation where the only thing that differs between two rows is a meaningless artificial key. That easily leads to unamusing data integrity issues at a later date.
It's important to identify the candidate keys during modelling. Maybe one gets selected as the primary, maybe an artificial key gets used instead, doesn't really matter, but then the candidate keys should get unique constraints defined on them so that the meaningless artificial key is not the sole measure of uniqueness in the table.
Quite right. I, on the other hand, have a serious problem with using a composite key and not using identities. Case in point (battle I lost), a customer database that uses a composite key of Customer ID and Customer Set ID (to match our enterprise database). Fine, I said - UK this composite, but put an identity on it. Nope. So - 20 bytes on each and every table that has a link into the master customer table (just about everything). And 200+ developer hours wasted dealing with the composite everywhere, in UDFs, stored procedures, UIs, and most recently in an XML based app. More coding work, more chance for developer error, more debugging, more of everything that wouldn't have been necessary by adding about 4Mb to the database (~0.7%).