SwayneBell (8/18/2010)

---

Hi Daniel;

That seems a perfectly sensible use for identity insert. However, in your example, I don't see it as inserting a duplicate value, more like reusing an identity value that was previously used. My question relates to why would you want to have two people (for example) with the same Identity value as their identifier?

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.