Is this a SQL Azure bug?

Question

I was trying to insert with linq in a table with identity column (1,1) and I got an exception.

The excepcion is correct because the value to inset has a legth of 16 chars and the column accept only 15 chars.

The funny is that with the exception, the value doesn't insert in the table(OK), but the identity value was incremeted(BAD).

Is that a bug?

Answer 1

This is normal behavior; the same will happen on a local SQL Server. When you attempt to insert a record in a table that has an identity column the next identity value is consumed; if somehow the insert is rejected due to some error, like an invalid data type or a referential integrity error, you have a gap in your sequence.

The only exception to that is if you have an invalid syntax in your query; at that point you don't get far enough in the process for SQL Server/SQL Azure to know you are doing an INSERT statement.

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com

Answer 2

good catch....:)  but i am not sure whether its Azure issue.

---

cpsingh

Answer 3

As Herve is mentioning, this is normal behavior and it happens on both SQL Azure and SQL Server.

The IDENTITY property will guarantee unique subsequent values on a column inside a single database, but it cannot guarantee the values to be consecutive. There are multiple scenarios that can lead to gaps between the values. One of them is the one mentioned by you, with an error happening during the insert. Another is caused by deletions. Yet another is caused by transaction rollbacks (described here).

If you need to make sure that the values inside a column are consecutive, then you will need to implement a custom mechanism for that. Just be aware that such a mechanism is prone to performance and locking issues.

---

Read my technical blog: ducons.com/blog | Connect with me on twitter: @fdumitrescu