Transactions Rollbacks and Table Rows Index

Apparently on SQL Server 2000 when you perform a Transaction and then execute the  Rollback statement you expect that all your database objects and data remain as they were before the Transaction, but this does not seems to be completely true when inserting rows into tables with an auto-increment property  also known as identity. for example if you insert 1000 rows into a new table during a transaction and then rollback the operation ,the current identity seed for that table will be 1001 instead of 1

The following T-SQL Code will Prove this

CREATE TABLE TEST

(

       ID INT IDENTITY(1,1) PRIMARY KEY 

       ,NAME VARCHAR(20)

       ,OCCUPATION VARCHAR(20)      

) 

-- ELIMINATE ANY PREVIOUS SEEDS

TRUNCATE TABLE TEST

 -- check 

DBCC CHECKIDENT('TEST', NORESEED) 

 -- start transaction

BEGIN TRAN

PRINT 'BEGIN TRANSACTION'

PRINT 'ADD FIVE RECORDS WITHIN TRANSACTION'

INSERT INTO TEST VALUES ('OSCAR','DEVELOPER')

INSERT INTO TEST VALUES ('JHON','DBA')

INSERT INTO TEST VALUES ('MARIO','QA ENGINEER')

INSERT INTO TEST VALUES ('SCOTT','MANAGER')

INSERT INTO TEST VALUES ('LUCAS','DEVELOPER')

PRINT 'ROLLBACK TRANSACTION'

--rollback

ROLLBACK

PRINT 'ADD ONE RECORD WITH OUT TRANSACTION'

INSERT INTO TEST VALUES ('LAST','DEVELOPER')

 SELECT * FROM TEST

-- check 

DBCC CHECKIDENT('TEST', NORESEED)

 And the Ouput shall look like this :

 Checking identity information: current identity value ’1′, current column value ’1′.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
BEGIN THE TRANSACTION
ADD FIVE RECORDS WITHIN TRANSACTION

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
ROLLBACK THE TRANSACTION
ADD ONE RECORD WITH OUT TRANSACTION

(1 row(s) affected)
ID          NAME                 OCCUPATION
———– ——————– ——————–
6           LAST                 DEVELOPER

(1 row(s) affected)

Checking identity information: current identity value ’6′, current column value ’6′.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 As You can see the ID for the last inserted Row is 6 when it should be 1, so i dont know if this is like this for a reason or this is SQL SERVER 2000 Bug. in some Scenarios this is not a problem but what i really would like to have my tables as they were and with out the need to execute the TRUNCATE TABLE statement.

Let me know your comments

About these ads