1

T-SQL:

BEGIN TRANSACTION

Insert (col1,col2,col3)

values (1,2,3)

After executing above t-sql and then ROLLBACK TRANSACTION MS-SQL does not decrements auto incremented column. Any idea why?

I found an answer here. Is this answer is applicable to MS-SQL?

shaadi
  • 161
  • 2
  • 4
  • 21

1 Answers1

2

Yes, SQL server will not "re-use" identity numbers once assigned. What's more, it is not guaranteed that the values will be consecutive:

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

You can use DBCC CHECKIDENT to change the identity values, but it is best to treat them as unique system IDs and do not assume anything about order.

You may also want to look at SEQUENCE which has some advantages over IDENTITY.

under
  • 2,519
  • 1
  • 21
  • 40
  • any idea - what is the best practice to insert rows to live database table? – shaadi Jun 22 '17 at 03:36
  • that would depend on the scenario. There are ETL tools for batch inserts (e.g. SSIS). Apps can use entity framework, OLE DB, JDBC... Best to ask a separate question and be more specific. – under Jun 22 '17 at 04:51