UMG Developer (9/24/2010)
Robert Frasca (9/24/2010)
I'm confused as to why you don't think a field with the IDENTITY attribute is not a surrogate key or identifier. The only reason to use the IDENTITY attribute is to create a surrogate key or identifier. It's not possible to create duplicate values either as the value automatically increments by the value in the second position of the declaration.Did I miss something here?
Yes, IDENTITY does not guarantee non-duplicate values by itself. (A common misconception.)
As Jeff Mason said you can use SET IDENTITY_INSERT ON and then insert rows with duplicate values unless the column has a CONSTRAINT defined on it to prevent it. (PRIMARY KEY, UNIQUE INDEX, etc.)
The reason to use IDENTITY is to get a sequential set of values by default for a column, it doesn't become valid as a surrogate key/identifier until it is guaranteed to not ever contain duplicates.
Well, I suppose you could do that but that defeats the purpose of using the identity attribute. I always make the column with the identity attribute the primary key. (Not necessarily the clustered key.) I can't think of any sensible scenario where I would want to allow duplicate identities. It's the IDENTITY attribute not the "quasi-unless-I-change-my-mind-Identity" attribute. Why bother? As soon as I allow that, it's just another integer column. Besides, the SET IDENTITY_INSERT ON isn't, in my opinion, really intended as a mechanism for inserting duplicates. It's a mechanism for inserting rows that already have identities. I've used it many times particulary when performing some sort of data integration.
"Beliefs" get in the way of learning.