Identity value in case the Insert fails + SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 321
In any cirumstances, the auto incremented value will NOT be set back to previous value if the INSERT fails .

Sample code for proving this,

--Table Creation
CREATE TABLE Table1 ( ID int identity( 1,1), Name varchar(15 )) -- the length of column is 15
GO

run below code for inserting one valid record, then INVALID data into NAME column on second insert.
BEGIN TRY
INSERT Table1 VALUES( 'First Record') -- length of name <15
INSERT Table1 VALUES( 'Second Record-Table1') -- -- length of name > 15
END TRY
BEGIN CATCH
RAISERROR ('Check the Identity value of table1', 15, 121)
END CATCH


Now, what will be the current identity value of table called Table1 ????

SELECT IDENT_CURRENT('Table1') Table1IDUsingIdentCurrent
PRINT 'Records in table1'
SELECT * FROM Table1

Output 
Table1IDUsingIdentCurrent
2

Records in table1
ID Name
1 First Record


NOTE: Even though the second insert has failed, the auto incremented value hadn't rollbacked.... If you try to insert 3rd record into the table, the identity value will be 3. In these cases, we will be having MISSED ID values in the table...

Comments or Responses

Login to post response