Duplicate value in Identity column

Question

Post reply

Duplicate value in Identity column

AppSup_dba

SSCrazy

Points: 2215
More actions
August 14, 2010 at 12:46 pm

#239028

Comments posted to this topic are about the item Duplicate value in Identity column
Cheers

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 1

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.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 4

ziangij (8/15/2010)
good one... duplicates can only be avoided by having a constraint in place...

...or by having a unique index in place.

Best Regards,

Chris Büttner

AppSup_dba SSCrazy Points: 2215 More actions · Answer 5

AppSup_dba

SSCrazy

Points: 2215

August 16, 2010 at 1:41 am

#1207189

And i thought i only didnt knew it 🙂

Cheers

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

Good Question...

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

pjdiller SSC Eights! Points: 977 More actions · Answer 7

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 🙂

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

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.

SanDroid SSChampion Points: 10068 More actions · Answer 9

Only one person can insert identity on a column at a time, so WE can not... :hehe:

SanjayAttray SSChampion Points: 13157 More actions · Answer 10

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.

SQL DBA.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

SanjayAttray (8/16/2010)
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.

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rose Bud Hall of Fame Points: 3147 More actions · Answer 12

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Nice question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OCTom SSChampion Points: 11785 More actions · Answer 14

SanjayAttray (8/16/2010)
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.

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.