5

When I try to insert a new row in the table, if there is any exception occurs in this transaction then data is rollback.

Now when a new entry is succesfully inserted next time, AutoIncrement id is updated with next value. Means there is Gap between two consequetive Unique Id in the table.

Is there any valid way to overcome this problem?

Thanks in advance

user1001101
  • 172
  • 1
  • 3
  • 8
  • You have to use **TRY,CATCH BLOCK** – Prahalad Gaggar May 03 '13 at 12:37
  • 4
    In what way is this a "problem" in the first place? SQL Server does not do this. See [this answer](http://stackoverflow.com/a/2829097/1324345) for a quick explanation and [this one](http://stackoverflow.com/a/2829328/1324345) for a real problem scenario. If you absolutely require that there be no gaps in your IDs, you will have to write your own logic for it - and then figure out what to do when you delete a record too. – alroc May 03 '13 at 12:38
  • 4
    The only problem to be overcome is the one in your expectations. Identity columns should be thought of as opaque blobs that, just be coincidence, can be stored in `int` variables. The actual *values* contained in these columns should, as much as possible, be ignored. – Damien_The_Unbeliever May 03 '13 at 12:42

1 Answers1

13

The answer has to be said - no.

The whole idea of IDENTITY columns is to not be meaningful, and to be transaction agnostic - so that the numbers can be dished out without care of other transactions rolling back or not. Imagine a 1000 insert per second system being held up for 10ms for each transaction (insert) to decide whether it will commit! (fyi 10ms * 100 = 1s)

Note: In SQL Server 2012 (latest SP/patch level at time of writing), there is a "feature" noted here on Connect related to identities.

Also even prior to 2012, you don't even need to rollback to consume an IDENTITY value - see here https://stackoverflow.com/a/16156419/573261

This applies to other major RDBMS as well for the same reasons, e.g.

PostgreSQL sequences

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

(emphasis mine)

Community
  • 1
  • 1
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • I'm 99.9% sure that should MySQL or PostgreSQL fail, they both roll the # back. I'm not sure why SQL Server can't do this but they can. – Phill May 03 '13 at 12:37
  • I agree. Doing a simple "select mySequence.NextVal from dual;" will "use up" the value. Try it out. run this query 3 times in your oracle world. – granadaCoder May 03 '13 at 13:21
  • @RichardTheKiwi I just tested it to make sure. PostgreSQL validates the insert before inserting it or something. If you create a varchar(10) column, insert a string with more than 10 char, then insert a valid record, you get ID 1. In SQL Server you get ID 2. – Phill May 03 '13 at 13:53
  • @Phill I specifically drew a line to leave the PostgreSQL note on its own. It refers to the gaps in sequences, not to the reproducibility of either of the two SQL Server quirks. – RichardTheKiwi May 03 '13 at 13:55
  • @RichardTheKiwi - ahh ok. The link you had didn't make much sense to me. The notes in Serial make more sense to me - http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-SERIAL good to note tho. Cheers. – Phill May 03 '13 at 13:59