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

1. Why is it increasing identity values even when transation is rolling back ?
2. 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.

Answer 1

1. 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
2. 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.