Identity Key losing count after insert failure

Question

I am using MVC4 whit entity framework 5, when server return error of insert because the table have a unique e-mail, I dont wanna lose id numbers. I need to check if this e-mail exists before try insert or entity framework have any another way to cover any failure error and reset the Identity Seed counter. Thanks

Why do you not want to lose numbers? An `IDENTITY` column does not guarantee consecutive numbers, see [this question](http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values) and the [documentation](http://msdn.microsoft.com/en-us/library/ms186775.aspx). — Pondlife, Apr 11 '13 at 19:20
Identify values increment even for a failed insert *by design*, so that identity inserts can be done concurrently. There should be no reason why you need your identity values to be sequential - they should serve only as a unique identifier. If you do need a sequential numbering of your rows, you can determine this at query time using `ROW_NUMBER()`. — Michael Fredrickson, Apr 11 '13 at 19:22
I am warning about bigint get max number before the number of rows is completed. So to know too, looking for good practices. — Lucas Rodrigues Sena, Apr 11 '13 at 19:47
Ehh, do I get this well, you ran out of available numbers for bigint? (9,223,372,036,854,775,807). Anyway, [IDbSetExtensions.AddOrUpdate](http://msdn.microsoft.com/en-us/library/hh846514%28v=vs.103%29.aspx) is a convenient method in this scenario. — Gert Arnold, Apr 11 '13 at 20:49
The maximum `bigint` value is [2^63-1](http://msdn.microsoft.com/en-us/library/ms187745.aspx), are you really worried about reaching that limit? The best practice is to simply to use identity values without worrying about what they are; they're just key values that have no meaning of their own. — Pondlife, Apr 11 '13 at 20:51

score 0 · Accepted Answer · answered Mar 06 '14 at 20:47

As shown in this article, there is no way to prevent gaps in the identity generated numbers, one of the main reasons;

To implement the identity value management transactional, a session would need to take a lock on the identity value itself for the duration of the transaction. That would force all inserts to be executed consecutively and concurrency would be practically non-existent.

About the shortage on bigint values, in most cases your DB will face a great number of problems before that gets near to happen.

Hope this was helpful.

Identity Key losing count after insert failure

1 Answers1