andrewd.smith (8/18/2010)
I can imagine some reasonable uses for an IDENTITY column where duplicates are allowed / expected.Say we have some sort of versioning system where the version number comprises a major number and a minor number. The business rules state that the minor version number is reset whenever the major version number changes. The decision to change major version numbers is triggered by an explicit user action and is infrequent, but the minor number should increment automatically whenever any change is logged (by inserting a new table row) which is expected to be a frequent occurrence. The minor number could be modelled reasonably by an IDENTITY column that is reset to 0 whenever the major number is incremented. The table that implements this versioning system could have a structure something like the following:
CREATE TABLE dbo.Version (MajorNumber int NOT NULL,
MinorNumber int NOT NULL IDENTITY(0, 1),
DateStamp datetime NOT NULL DEFAULT(GETDATE()),
Comment nvarchar(1000) NULL,
CONSTRAINT PK_Version PRIMARY KEY CLUSTERED (MajorNumber, MinorNumber),
CONSTRAINT CK_VersionNumber CHECK (MajorNumber >= 1 AND MinorNumber >= 0)
)
No, that would not be a good idea. The IDENTITY property does not guarantee that there will not be any gaps. And in practice, there will be gaps. If the business requires consecutive numbering, IDENTITY is not a good option.
There are some good reasons for manually inserting values in an IDENTITY column, as already posted to this discussion. But I see no good reasons to allow duplicate values in the IDENTITY column. And that is exactly what makes this QotD so valuable - as a reminder to always add a PRIMARY KEY or UNIQUE constraint when we add the IDENTITY property to a column, because the IDENTITY property alone does not guarantee uniqueness.