Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Duplicate value in Identity column


Duplicate value in Identity column

Author
Message
SteveBell
SteveBell
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 1537
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?
daniel.noble
daniel.noble
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 111
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.
andrewd.smith
andrewd.smith
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 3232
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)
)


Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 11540
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
andrewd.smith
andrewd.smith
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 3232
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.
skrilla99
skrilla99
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 Visits: 1321
Yow... Good question.



Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 237
I thought identity_insert would allow duplicates but google said no....
You can't always trust information on internet :-D
BowieRules!
BowieRules!
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 283
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.
zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 259
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search