Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unhappy Unanswered: Transactions and IDENTITY columns

    Hi,
    I noticed that tables containing IDENTITY columns skip IDENTITY values when a transaction is rolled back. Is there any way to avoid this? By "avoid" I mean make the table continue generating IDENTITY values from where the last COMMITTED insert took place.


    Example:

    CREATE TABLE ATable (A INT IDENTITY, B INT);

    INSERT INTO ATable (B) VALUES (39);
    INSERT INTO ATable (B) VALUES (51);
    INSERT INTO ATable (B) VALUES (62);
    INSERT INTO ATable (B) VALUES (93);
    INSERT INTO ATable (B) VALUES (10);

    ATable will now contain the following values:

    A___B___
    1___39__
    2___51__
    3___62__
    4___93__
    5___10__

    Now let's say I do this:

    BEGIN TRAN MyTranName;
    INSERT INTO ATable (B) VALUES(44);
    INSERT INTO ATable (B) VALUES(32);
    INSERT INTO ATable (B) VALUES(11);
    ROLLBACK TRAN MyTranName;

    The three new rows won't (obviously) be in the table as my TRANSACTION was rolled back.

    However, if I do this :
    INSERT INTO ATable (B) VALUES (28);
    INSERT INTO ATable (B) VALUES (52);


    ...My table will now contain the following values:

    A___B___
    1___39__
    2___51__
    3___62__
    4___93__
    5___10__
    <I>NEW ENTRIES</I>
    9___28__
    10__52__

    Is there a way to have SQL-Server continue from where it left off (IDENTITY VALUES 6, 7, 8) without having to generate the numbers manually or occasionnally turning on IDENTITY_INSERT on the relevant table(s)?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, but you really shouldn't use IDENTITY columns that way. The uniqueness of identity values are all that you should count on... The sequence is really system defined, and will probably change in future versions.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    You could generate your own on the fly.

    SELECT A, (SELECT Count(A) FROM myTable T2 WHERE T2.A <= T1.A) AS ID FROM myTable T1

    Keep in mind though, that this ID would change if you delete a record from the table, so you can't rely on it, and since you can't rely on it, you can't use it as a foreign key, unless your hobbies include rectifying corrupt data.
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    totally agree with pat here

    the best way to solve the problem is to allow the gaps in your numbers

    it's what i call the "zero-effort" option, and it allows you to take the rest of the afternoon off

    what was the reason for you wanting to "re-use" the numbers?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    84
    Rudy:
    Invoicing operation. I start a transaction, do necessary work for invoicing a client account at a time, commit at the end. If, however an error is found during the process, the client application warns the user who can decide to cancel the invoicing operation, or ignore the error(s). (basically skip that particular account (the accounts that are not invoiced are logged, so that the user can fix the problem, and invoice those account in another batch.

    Should I use a Max() + 1 set up here (which I avoid like the plague)?!?!?

    Thanks a million.

  6. #6
    Join Date
    Oct 2003
    Posts
    84
    oh, I didn't anwser the question rudy. I don't want to reuse the numbers, I just don't want any gaps. I wanted to use the IDENTITY column as an InvoiceID.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, sorry, i asked the wrong question

    the real question: why don't you want any gaps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    It really shouldn't matter if there are gaps in the InvoiceID if you have something in place to insure that deletes don't happen (history table where deleted records get moved to for example). If you insist in no gaps though, break the identity column into it's own table (invoice_generator).

    The procedure that generates the InvoiceID can INSERT a new field. When the Invoicing process has completed successfully, you can then enter a date or change a bit field to indicate it's being used. The procedure can be built to either look for and MIN(InvoiceID) WHERE date IS NULL or INSERT into the table a new record.

    Just a thought (very convoluted one, but a thought).
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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