Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Duplicate value in Identity column Expand / Collapse
Author
Message
Posted Wednesday, August 18, 2010 7:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:59 AM
Points: 209, Visits: 1,537
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?
Post #971095
Posted Wednesday, August 18, 2010 7:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 13, 2012 3:12 AM
Points: 373, Visits: 84
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.
Post #971114
Posted Wednesday, August 18, 2010 4:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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)
)

Post #971517
Posted Thursday, August 19, 2010 12:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Post #971643
Posted Thursday, August 19, 2010 6:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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.
Post #971812
Posted Tuesday, September 21, 2010 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 04, 2014 6:01 PM
Points: 1,117, Visits: 1,217
Yow... Good question.


Post #990547
Posted Sunday, September 26, 2010 10:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
I thought identity_insert would allow duplicates but google said no....
You can't always trust information on internet
Post #993427
Posted Tuesday, October 05, 2010 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:27 PM
Points: 198, Visits: 257
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.
Post #998613
Posted Wednesday, March 07, 2012 2:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 9:09 AM
Points: 483, Visits: 242
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
Post #1263281
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse