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
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
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.