Duplicate value in Identity column

  • AppSup_dba

    SSCrazy

    Points: 2215

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

    Cheers

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    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

    SSCertifiable

    Points: 7156

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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

    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

    And i thought i only didnt knew it 🙂

    Cheers

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good Question...

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

  • pjdiller

    SSC Eights!

    Points: 957

    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

    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

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

  • SanjayAttray

    SSChampion

    Points: 13157

    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: 258965

    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

    SSCrazy

    Points: 2971

    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: 281243

    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: 11755

    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.

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

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