Why rolling back transation in SQL Server increases seed values

Question

I am doing some initial development in SQL Server 2008 version and because it was test script I had script the queries in transaction to not affect the database tables actually.

But it seems the records are not entered into database but the seed value is incremented each time I executed the script.

T-SQL script:

Begin Tran

DECLARE @hdoc int
DECLARE @doc VARCHAR(MAX)
SET @doc =
'<?xml version="1.0" encoding="UTF-8"?>
<root>
   <places>
      <Row>
  <Field_0>53.570438</Field_0>
  <Field_1>-113.390992</Field_1>
  <Field_2>Address details 1</Field_2>
  <Field_3>Lab 1</Field_3>
</Row>
<Row>
  <Field_0>53.542062</Field_0>
  <Field_1>-113.528646</Field_1>
  <Field_2>Address details 2</Field_2>
  <Field_3>Lab 2</Field_3>
</Row>
   </places>
</root>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

INSERT INTO dbo.Laboratories
(Name, Latitude, Longitude, [Image], ContentDetails, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy)
SELECT ROW_NUMBER() OVER (ORDER BY Name)
, cast(Latitude as decimal(11,8)), cast(Longitude as decimal(11,8)), null, Content, GETDATE(), 1, null, null
FROM OPENXML (@hdoc, '/root/places/Row', 2)
      WITH (
         Name  nvarchar(100) 'Field_3'
            ,Latitude varchar(20) 'Field_0'
            ,Longitude varchar(18) 'Field_1'
            ,Content nvarchar(100) 'Field_2'
            )

EXEC sp_xml_removedocument @hdoc

select * from dbo.Laboratories

rollback tran

I have following script to identify the current seed values

SELECT 
    IDENT_SEED(TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
    TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

Which is referenced from here

Basically question is

Why is it increasing identity values even when transation is rolling back ?
How to fix this problem from next time to not affect the seed values when transaction is not committed?

PS: I can reseed the identity that is ok, I know about that.

possible duplicate of [Why are there gaps in my IDENTITY column values?](http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values) — Pondlife, May 16 '13 at 15:43
No it does not seem same, I have transaction in place where I think nothing should be affected when transaction is rolled back.. — Harsh Baid, May 17 '13 at 04:19
Then your question may be a duplicate of [this one](http://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback) — Pondlife, May 17 '13 at 16:42
My question is for ms sql 2008 version otherwise it would have been duplicate. — Harsh Baid, May 19 '13 at 05:24

score 2 · Accepted Answer · answered May 16 '13 at 07:34

2

Once the identity value is assigned, it stays assigned whether rolled bakc, failed committed, whatever. This applies generally, to MySQL AUTOINCREMENT and the SEQUENCE feature too
You can't. This is by design

It should make no difference it the IDENTITY has gaps or not. An IDENTITY is just an arbitrary surrogate value that has no external meaning: it is there to complement suboptimal natural keys only.

answered May 16 '13 at 07:34

gbn

422,506
82
585
676

For (2):- he can use reseed in the catch section to fix(Manual fix) this issue. – Maximus May 16 '13 at 08:49
@Sivakumar: What if another transaction comes in and gets the next number before the RESEED happens? – gbn May 16 '13 at 09:00
The table is not accessible until the current transaction ends. We can do reseed before we rollback the transaction as the rollback is not going to change the seed value. Correct me if I'm wrong. – Maximus May 16 '13 at 09:04
Thanks for the information and reply. I learned something today. :) – Maximus May 16 '13 at 09:46

Why rolling back transation in SQL Server increases seed values

1 Answers1