SQL Clone
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
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: 1118 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 (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4942 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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34201 Visits: 13108
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
andrewd.smith
andrewd.smith
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4942 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1322
Yow... Good question.



Dhruvesh Shah
Dhruvesh Shah
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: 1213 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-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 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
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: 1102 Visits: 263
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