SQL Server Performance

IDENTITY VALUE RESET

Discussion in 'SQL Server 2005 General DBA Questions' started by SQL2000DBA, Aug 9, 2007.

  1. SQL2000DBA New Member

    Hi,
    I am facing typical problem where in i am not able to rollback the identity key value. In my opinion anything i do inside the transaction and whenever system exception raised, database should rollback everything. Here is my codeCREATE
    TABLE T1(
    CustomerID INT IDENTITY(1,1) NOT NULL,CustomerName
    varchar(10) )GOCREATE
    PROC Test @CustomerName varchar(20)AS
    BEGIN TRY
    BEGIN
    TRANINSERT
    INTO T1VALUES
    (@CustomerName)COMMIT TRAN
    END
    TRYBEGIN CATCH
    SELECT
    @@ERRORROLLBACK TRAN
    END
    CATCHGOEXECUTE
    Test 'Customer1' -------This work fine here identity value 1GOEXECUTE Test 'Customer12345' /***This gives error and identity key set to 2 but i am rolling back statement here it shoudl rollback to last value 1
    ***/GOEXECUTE
    Test 'Customer12' -------This work fine but here identity value is 3 not 2GOPlease provde me your suggestion
  2. thomas New Member

    This is the nature of identity columns, rolling back inserts into them does not reset the identity value. You can use DBCC CHECKIDENTITY with a RESEED parameter, but this shouldn't be necessary. Do the gaps bother you?
  3. dineshasanka Moderator

    DBCC CHECKIDENT ('TableName', RESEED, 0)
  4. ndinakar Member

    As thomas said, thats the way the identity column works. whether the transaction succeeds or not, once the number is allocated its gone. Even if the transaction fails, the next insert will get the next available number, and there could be gaps in the id values for such failed transactions.
  5. FrankKalis Moderator

    Piggy-backing onthe previous replies. When the value itself has any meaning to you, then the IDENTITY property is mostly likely not the way to go for you. But there are really very few reasons for maintaining a "perfect" sequence.
  6. ndinakar Member

    Frank
    I like that shark avatar you have. And I stole it.. [:D].. but I will use it on other forums so ppl here dont get confused..
  7. FrankKalis Moderator

    Fine, no problem with me. [:)]
    I think this one is a little bit less frightening than the former one.
  8. thomas New Member

    Isn't it the shark from Finding Nemo?
  9. FrankKalis Moderator

    Yes, that's Bruce. [:)]
  10. Madhivanan Moderator

    AJITH123
    use
    DBCC CHECKIDENT ('SAMPLE11', RESEED, -1)
    instead of
    DBCC CHECKIDENT ('SAMPLE11', RESEED, 0)
  11. AJITH123 Member

    Oh okThanks for your help :)
  12. satya Moderator

    Why don't you upload yr fav. avatar [;)]
    [quote user="thomas"]
    Isn't it the shark from Finding Nemo?
    [/quote]
  13. AJITH123 Member

    I do have a question regarding the identity reset issue, the below said code will explain thet,
    CREATE TABLE SAMPLE11
    ( ID INT IDENTITY (0,1)
    , NAME varchar(100)
    )
    INSERT INTO SAMPLE11 VALUES('A')
    INSERT INTO SAMPLE11 VALUES('B')
    --SELECT * FROM SAMPLE11
    DELETE FROM SAMPLE11
    DBCC CHECKIDENT ('SAMPLE11', RESEED, 0)
    INSERT INTO SAMPLE11 VALUES('A')
    INSERT INTO SAMPLE11 VALUES('B')
    SELECT * FROM SAMPLE11
    After execting this code i am getting the resut as,
    ID NAME
    1 A
    2 B
    But it should not be like that, it shouild be like
    ID NAME
    0 A
    1 B How it is happening ?? Plese reply back
  14. FrankKalis Moderator

    Change the RESEED value to -1. The next used number thereafter will be 0.
  15. Madhivanan Moderator

    [quote user="FrankKalis"]
    Change the RESEED value to -1. The next used number thereafter will be 0.
    [/quote]
    [sniped]
    My internet connection is slow [:)]
    Yes this shark is less frighting than the other one [;)]

Share This Page