Identity SQL Server Problem

I have use Identity on ID primary key. And then I insert some data. For example.

Data 1 -> Add Successful without error. ID 1

Data 2 -> Add Successful without error. ID 2

Data 3 -> Add Fail with error.

Data 4 -> Add Fail with error.

Data 5 -> Add Successful without error. ID 5

You can see that ID has jump from 2 to 5.

Why ?? How can solve this ??

Why would that be a problem ?

Normally, you'll use an identity in a primary key column. Then, this primary key is a surrogate key, which means that is has absolutely no business value / business meaning. It is just an 'administrative' fact, which is necessary in order that the database can uniquely identify a record. So, it doesn't matter what this value is; and it also doesn't matter that there are gaps. Why do you want them to be consecutive.

And, suppose that they are consecutive -that no gaps appear when an insert fails- what would you do when you delete a row, and insert one later on ? Would you fill in the gaps as well ?

this is by design, sql server first increments the counter and than tries to create row, if it fails transaction (there is implicit transactions always) is roll backed but auto increment value is not reused. this is by design and I would be very surprised to see that it can be avoided (eventually you could call some command and reset the value to current maximum). You can always use the trigger to generate this values, but this has performance implications, usually you should not care about the value of auto_increment its just an integer, you would have the same situation later in your application if th

If an insert failed, you can, for the next insert, use set identity_insert mytable on and calculate the next identity by hand, using max(myfield)+1. You might have concurrency issues though.

But this is a cludge. There's nothing wrong with gaps.

@Frederik answered most of it -- I would just add that you are mixing up primary keys and business keys. An invoice (or whatever) should be identified by an invoice number -- a business key which should have a UNIQUE column in the table. The primary key is here to identify a row in the table and should be used by the database (to join ..) and by DBAs only.

Exposing primary keys to business users will end up in trouble and the database will sooner or later lose referential integrity -- always does, people are creative.