August 16, 2010 at 2:34 pm
OCTom (8/16/2010)
Perhaps Microsoft should change it so that a PK or unique constraint must accompany an identity column? Like you say, it doesn't make sense to have it this way and is a potential problem.
I don't see that happening. It could potentially break existing code that relies on identity values not being unique (why? don't ask me - people do the craziest things in SQL Server). And if MS would ban every option that you can use to shoot yourself in the foot, there wouldn't be a lot left...
August 17, 2010 at 12:34 am
Nice question.
August 17, 2010 at 4:57 am
Yep,
I got this one wrong also.
However, it begs the question, "why would you want an identity column in the first place if you intend to insert a duplicate value?"
August 17, 2010 at 5:09 am
In one scenario we had to copy some data from live DB to test DB. Since the table was having an identity column we were not able to make the data consistent hence we used the above mentioned feature...
:w00t:
Cheers
August 18, 2010 at 3:32 am
dbowlin (8/16/2010)
Identity insert can be very dangerous. For a long time I couldn't see a use for it. Then I needed it to get 2 tables in 2 different databases into sync after they had fallen out of sync for some unknown reason. It saved me a lot of effort.
I move data from a Live table (with an identity on the PKey) into an Archive table. Just occasionally I need to move a row back into the Live table. Identity insert allows me to move it back with its original ID, thus maintaining references to this row from other tables. So there's one use for it.
August 18, 2010 at 7:00 am
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?
August 18, 2010 at 7:18 am
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?
You're right, and I can't think of any good reasons for inserting duplicate values into an identity column either. But I don't object to having the freedom to do so. It's useful to know that if uniqueness is required it needs to be enforced separately though. It's something I'd not considered before.
August 18, 2010 at 4:46 pm
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)
)
August 19, 2010 at 12:55 am
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.
August 19, 2010 at 6:40 am
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.
I agree if gaps are not acceptable, but if the possibility of gaps in the minor number is not a problem for the business rules, then I still believe that this is a reasonable solution.
September 21, 2010 at 10:48 am
Yow... Good question.
September 26, 2010 at 10:57 pm
I thought identity_insert would allow duplicates but google said no....
You can't always trust information on internet 😀
October 5, 2010 at 11:40 am
I would add that you can also insert duplicate into identity by reseeding IDENTITY.
dbcc checkident(table_name,reseed,0)
and it will start from beginning again, unless as mentioned above you have unique key on the column.
March 7, 2012 at 2:19 pm
Good tricky question and yes, duplicates are possible to occur though depending on their intended purposes, it may usually not be recommended and therefore different sequential numbers ranges are used in such a cases that duplicates are to be avoided.
Thanks
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply