IDENTITY property does not reuse values. From IDENTITY (Transact-SQ) - Remarks (emphasis added by me):
The identity property on a column does not guarantee the following:
Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.
Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.
Consecutive values after server restart or other failures -SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.
Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.
These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations. If you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application.
This means that if you
INSERT a row and the transaction is rollbacked, for whatever reason, that seed will not be reused. The same is true for if you a delete a row; the existing rows won't be reseeded and then the "new" next value used; there will be a gap in your numbers.
The value of the
IDENTITY is meant to purely be used from a relational point of view, not to create consecutive values in your table that will be maintained. If you want consecutive values at run time you should use
ROW_NUMBER. If you want to store them, you'll be better off looking at a