SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ankur / Duplicate value in Identity column / Latest Posts

RE: Duplicate value in Identity column

zymos — Wed, 07 Mar 2012 14:19:01 GMT

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

RE: Duplicate value in Identity column

BowieRules! — Tue, 05 Oct 2010 11:40:07 GMT

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.

RE: Duplicate value in Identity column

Dhruvesh Shah — Sun, 26 Sep 2010 22:57:04 GMT

I thought identity_insert would allow duplicates but google said no.... You can't always trust information on internet :-D

RE: Duplicate value in Identity column

skrilla99 — Tue, 21 Sep 2010 10:48:20 GMT

Yow... Good question.

RE: Duplicate value in Identity column

andrewd.smith — Thu, 19 Aug 2010 06:40:10 GMT

[quote]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 [b]will[/b] be gaps. If the business requires consecutive numbering, IDENTITY is not a good option.[/quote]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.

RE: Duplicate value in Identity column

Hugo Kornelis — Thu, 19 Aug 2010 00:55:51 GMT

[quote][b]andrewd.smith (8/18/2010)[/b][hr]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:[code="sql"]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))[/code][/quote]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 [b]will[/b] 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.

RE: Duplicate value in Identity column

andrewd.smith — Wed, 18 Aug 2010 16:46:38 GMT

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:[code="sql"]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))[/code]

RE: Duplicate value in Identity column

daniel.noble — Wed, 18 Aug 2010 07:18:25 GMT

[quote][b]SwayneBell (8/18/2010)[/b][hr]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?[/quote]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.

RE: Duplicate value in Identity column

SteveBell — Wed, 18 Aug 2010 07:00:57 GMT

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?

RE: Duplicate value in Identity column

daniel.noble — Wed, 18 Aug 2010 03:32:14 GMT

[quote][b]dbowlin (8/16/2010)[/b][hr]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.[/quote]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.

RE: Duplicate value in Identity column

AppSup_dba — Tue, 17 Aug 2010 05:09:04 GMT

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:

RE: Duplicate value in Identity column

SteveBell — Tue, 17 Aug 2010 04:57:08 GMT

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?"

RE: Duplicate value in Identity column

VM-723206 — Tue, 17 Aug 2010 00:34:47 GMT

Nice question.

RE: Duplicate value in Identity column

Hugo Kornelis — Mon, 16 Aug 2010 14:34:35 GMT

[quote][b]OCTom (8/16/2010)[/b][hr]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.[/quote]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...

RE: Duplicate value in Identity column

OCTom — Mon, 16 Aug 2010 14:08:52 GMT

[quote][b]SanjayAttray (8/16/2010)[/b][hr]Yes you can insert but should be avoided by having a unique constraint or Pkey on identity column. Since, it doesn't make much sense, if you want to put duplicate value in identity column then why add identity option to column int. Just leave it as is.[/quote]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.

RE: Duplicate value in Identity column

SQLRNNR — Mon, 16 Aug 2010 08:50:46 GMT

Nice question.

RE: Duplicate value in Identity column

Rose Bud — Mon, 16 Aug 2010 08:39:47 GMT

Interesting. Very surprised to learn you can enter duplicate values when inserting explicit values. Further surprised to learn that you can get duplicate values by resetting the identity seed, according to this person:http://beyondrelational.com/blogs/jacob/archive/2009/02/03/sql-server-identity-why-do-i-have-duplicate-identity-values.aspx

RE: Duplicate value in Identity column

Koen Verbeeck — Mon, 16 Aug 2010 08:39:35 GMT

[quote][b]SanjayAttray (8/16/2010)[/b][hr]Since, it doesn't make much sense, if you want to put duplicate value in identity column then why add identity option to column int. Just leave it as is.[/quote]I think the idea behind this question is to warn people that just using IDENTITY is not enough. Not everybody knows that duplicate entries can still happen with an IDENTITY, so that is a valuable lesson learned with this question.

RE: Duplicate value in Identity column

SanjayAttray — Mon, 16 Aug 2010 07:56:38 GMT

Yes you can insert but should be avoided by having a unique constraint or Pkey on identity column. Since, it doesn't make much sense, if you want to put duplicate value in identity column then why add identity option to column int. Just leave it as is.

RE: Duplicate value in Identity column

SanDroid — Mon, 16 Aug 2010 07:09:03 GMT

Only one person can insert identity on a column at a time, so [b][i][u]WE[/u][/i][/b] can not... :hehe:

RE: Duplicate value in Identity column

Daniel Bowlin — Mon, 16 Aug 2010 06:43:33 GMT

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.

RE: Duplicate value in Identity column

pjdiller — Mon, 16 Aug 2010 06:26:32 GMT

I have to admint I wouldn't have gotten this one right, except for a similar question a few months ago. I still don't understand why one would want to create an identity and then break the rules of the identity. Seems like trouble :-)

RE: Duplicate value in Identity column

Danny Ocean — Mon, 16 Aug 2010 04:57:43 GMT

Good Question...

RE: Duplicate value in Identity column

AppSup_dba — Mon, 16 Aug 2010 01:41:26 GMT

And i thought i only didnt knew it :-)

RE: Duplicate value in Identity column

Christian Buettner-167247 — Mon, 16 Aug 2010 01:25:14 GMT

[quote][b]ziangij (8/15/2010)[/b][hr]good one... duplicates can only be avoided by having a constraint in place...[/quote]...or by having a unique index in place.

RE: Duplicate value in Identity column

Koen Verbeeck — Mon, 16 Aug 2010 00:26:33 GMT

Nice question. Usually an Identity column is used as a primary key (and thus having a unique clustered index), which will prevent the insertion of duplicate values.

RE: Duplicate value in Identity column

ziangij — Sun, 15 Aug 2010 23:06:17 GMT

good one... duplicates can only be avoided by having a constraint in place...

RE: Duplicate value in Identity column

malleswarareddy_m — Sun, 15 Aug 2010 22:17:05 GMT

hi,I know that we can add explicit values to an identity column using Identity_Insert on. but i thought that identify column will not allow duplicate columns.good question.lost one point but learned one new point.

Duplicate value in Identity column

AppSup_dba — Sat, 14 Aug 2010 12:46:54 GMT

Comments posted to this topic are about the item [B]Duplicate value in Identity column[/B]