Post reply

IdentityColumn is Duplicated ?

  • July 30, 2008 at 10:56 pm

    #93690

    In my web based application Identity column is duplicated when simultaneously record are inserted by different machine

    (i think so) i will solution for handling this condition .

  • July 31, 2008 at 12:12 am

    #851004

    Identity column is duplicated

    Do you have any triggers?

    If @@IDENTITY is used, replace it with SCOPE_IDENTITY()

  • July 31, 2008 at 5:37 am

    #851178

    When you say duplicated, do you mean in the data? I've never seen that in normal functions, it must be a bug or you're not using the IDENTITY setting and are instead using some sort of trigger or function. It's also possible you've hit a bug.

    If it's a perceived duplication, meaning, the record goes in, but the values you're retrieving are duplicated, then I think the post above has the answer.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 31, 2008 at 5:54 am

    #851184

    The data in the Identity Column can be duplicated if it is Not a PK or the column is Not Uniquely Indexed. This can be checked by using

    SET IDENTITY_INSERT dbo.TableName ON

    But, generally, the Indentity Columns are defined as PKs (in most of the cases).

    I think the duplication can be stopped by avoiding SET IDENTITY_INSERT dbo.TableName ON while inserting data and let the sql server decide the value to be inserted in the Identity column.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • July 31, 2008 at 6:18 am

    #851195

    IDENTITY columns do not duplicate, even without a constraint, when operating normally. You can force duplicates in by using IDENTITY_INSERT or by doing a RESEED, but the normal behavior is one of constantly increasing values, without duplication because of multiple inserts. None of these circumstances were in the OP.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 4, 2008 at 12:47 am

    #852529

    You are right Grant. I was just making clear that IDENTITY column may have duplicate values IFF the IDENTITY_INSERT is set to ON while inserting a record OR if RESEED option is used (as you mentioned, I have not checked it).

    OP might be using one of these options while inserting records. Otherwise I donot see any resason for duplication in an IDENTITY Column.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • August 5, 2008 at 10:03 am

    #853296

    We are assuming the OP is saying;-

    when a simultaneous insert occurs from two client appliactions

    the id of the two inserted records in a/the table are identical.

    This indicates no existing unique constraint on the coulmn, therfore its not a primary key.

    and

    The column in question probably is not set set for IDENTITY.

    It sounds like a "get last value + 1" type insert........

    Post the Create Table script please....:D

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • August 6, 2008 at 1:19 pm

    #854085

    Hi

    you can check your Web page and prevent F5 , disable SUBMIT button(when submits)

    Valentin

  • August 11, 2008 at 11:10 am

    #856261

    If you think your data is corrupted, look for DBCC CheckIdent


    paul

  • August 17, 2008 at 6:01 pm

    #859204

    dolly (7/30/2008)

    In my web based application Identity column is duplicated when simultaneously record are inserted by different machine

    (i think so) i will solution for handling this condition .

    I don't think it's an "IDENTITY" column at all... I think that's what they calling it and someone is using MAX+1 or some other method, perhaps like a sequence table, to create "ID's".

    Dolly... are you still there?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2008 at 1:18 am

    #859280

    From my post before yours Jeff I totally agree.

    --Shaun

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • August 18, 2008 at 5:14 am

    #859364

    Shaun McGuile (8/18/2008)

    From my post before yours Jeff I totally agree.

    --Shaun

    Spot on, Shaun! I missed that... sorry. Good to see I'm not the only one.

    Now, if the OP were only kind enough to respond. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2008 at 5:43 am

    #859375

    No kidding, drop the grenade & run...

    They could be doing something silly with reseed?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 18, 2008 at 6:35 am

    #859395

    Grant Fritchey (8/18/2008)

    No kidding, drop the grenade & run...

    They could be doing something silly with reseed?

    Thats not a grenade its a nuke......:D

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • August 18, 2008 at 5:37 pm

    #859746

    Shaun McGuile (8/18/2008)

    Grant Fritchey (8/18/2008)

    No kidding, drop the grenade & run...

    They could be doing something silly with reseed?

    Thats not a grenade its a nuke......:D

    COOL! Radioactive pork chops! I'm good with THAT! 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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