IDENTITY VALUE RESET

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

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?

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.

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

Isn't it the shark from Finding Nemo?

Oh okThanks for your help

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