Posted by: oxkr1320 | September 18, 2008

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


Responses

  1. Hola!! felicidades por tu blog. No entiendo de esto, pero creo que para la gente de tu medio debe ser muy interesante toda esta información. Un abrazo y te deseo mucho éxito en tu carrera.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: