When is a Sequence Number Not Sequential?

A sequence number or an identity column guarantees the following:

1. Number served is unique.
2. Number served is sequential (ascending order) - Identity columns guarantees this; sequence number in Oracle RAC mode doesn't, unless ORDER is used.

However:

1. It does not guarantee the sequence is gap-free
2. Sequence number/ identity columns do not partake in transactions

Number loss/ gaps happen due to the following:

1. Served number does not get utilised (typically due to transaction rollback)
2. Server restart/ failures - database servers tend to cache sequences in memory for performance reasons. E.g. Oracle Sequence number set-up to be CACHEd in memory.

According to Oracle 11gR2 Database Documentation: for the Sequence database concept, use of Oracle Sequence does not guarantee gap-free set of numbers.

 

If your application requires a gap-free set of numbers, then you cannot use Oracle sequences. You must serialize activities in the database using your own developed code.

Source: Oracle Database Documentation

According to the Microsoft TechNet library for SQL 2012: for the Identity SQL database property, in the events of server restart/ failures or transaction failures resulting in rollback, consecutive values cannot be guaranteed and therefore, gaps may be present.

• 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 a sequence generator with the NOCACHE option or use their own mechanism to generate key values.

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

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.

Source: Microsoft TechNet for SQL Server 2012

Serialization appears to be the only solution. However, serialization would be non-scalable and would incur a huge performance impact.