Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    15

    Unanswered: missing Identity values in primary Keys

    Hi, We are facing the following issue, several machines/users that are executing very often a command similar to :

    INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
    SELECT SCOPE_IDENTITY() AS Table_ID;

    Where TableName has a primary key defined as identity(1,1).
    and that Table_ID is being used as reference in others tables

    Microsoft SQL Server Enterprise Edition 8.00.2039 running on a Microsoft Windows NT 5.2 (3790)

    These queries are executed using different dababase users and among several diffrent apps

    The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.

    However, we need to understand why the Scope_Identity returns the Inserted ID if the INSERT action had failed.

    Any ideas, comments, suggestions would be REALLY appreciated.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by wzbn View Post
    However, we need to understand why the Scope_Identity returns the Inserted ID if the INSERT action had failed.

    Any ideas, comments, suggestions would be REALLY appreciated.
    Sounds like the identity (insert) was rolled back, but maybe a local variable was already declared and value was set. Which was used in the child (reference) tables.

  3. #3
    Join Date
    Mar 2003
    Posts
    7
    Quote Originally Posted by corncrowe View Post
    Sounds like the identity (insert) was rolled back, but maybe a local variable was already declared and value was set. Which was used in the child (reference) tables.
    That's exactly what it seems. However, we cannot understand either why the rollback is done or the SCOPE_Identity returns a number. If the record was rollbacked, why the SCOPE_Identity returns its ID?

    Can anybody explain us why this auto-rollback is performed (to try to fix it and get a solution). Thanks in advanced.

    Regards,

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Mc Brain, This is from the MSDN Library:

    "Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented."

    The Insert could fail but the Identity value would still be incremented and show up with a SCOPE_IDENTITY()

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using DRI will fix the "orphan" problem.

    The values in an Identity column are guaranteed to be unique, but are emphatically not guaranteed to be sequential. Gaps in the Indenty column values are expected when transactions are rolled back either explicitly or implicitly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •