Sybase ASE
All versions
Last updated: 15 February 2009
Identity gaps in Sybase ASE
 
Documentation about identity gaps
This page points to various documents related to 'identity gaps' in Sybase ASE. An 'identity gap' is a large, sudden, and unexpected jump of values in an identity column, usually as a result of a shutdown with nowait (although it can also occur as a result of certain dump/load scenarios). Identity gaps have long been problematic in ASE because they couldn't always be avoided, and were difficult to fix.
The following documents are available on this web site:
  • If you're not familiar with identity gaps, I recommend to first read the article "A better method for dealing with identity gaps" (from 1998). This describes the technical background of identity gaps as well as the classic solution and a workaround to avoid these gaps. The full article can be found here (HTML) and here (PDF).
    (note that ASE 12.5.0.3 and 12.5.1 include new features that make both the classic solution and the workaround redundant -- see next bullet)
  • In ASE 12.5.0.3 and 12.5.1, new features have been implemented to address the long-standing problems with fixing identity gaps. These features are described in the article "Identity gaps revisited" (from 2004), available here. This article also contains a good overview of how to handle identity gaps in different ASE versions.
  • In ASE 12.0 or later, you should always use the identity_gap feature for each permanent table with an identity column to limit the size of possible identity gaps. This is for prevention only: this feature doesn't do anything to fix identity gaps. Details are described here.
  • As of ASE 12.5.3, identity columns can also use int, smallint or tinyint datatypes, i.e.: create table t (a int identity).
  • As of ASE 15.0, identity columns can also use the new datatypes bigint, unsigned smallint, unsigned int and unsigned bigint, i.e.: create table t (a unsigned bigint identity).
  • In ASE 15.0.2, it is no longer needed to insert a row into a table to obtain an identity value: with the built-in function reserve_identity(), you can increment the identity counter by 1 or more units. For details, see the Quiz Question for December 2008.

Brief summary of identity gap issues
Identity columns (sometimes referred to as "auto-increment" columns) have long been a somewhat controversial topic in ASE. While the functionality of identity columns -automatically assigning sequential numbers while allowing great concurrency- is very useful, the implementation of certain aspects in ASE left quite a few things to be desired. In practice, this meant that there is a risk of running into "identity gaps" : these are occasions where the identity value suddenly make large jumps, like from 10031 to 5000002 instead of to 10032, usually as a result of a shutdown with nowait (as well as certain dump/load scenarios, although these tend to occur less frequently).
Because shutdown with nowait cannot always be avoided, identity gaps may occur at some point, and they can cause real problems for applications that do not expect such large numbers.
For a long time, ASE unfortunately did not have any features to easily repair an identity gap: the repair procedure documented by Sybase was a bit clumsy and could cause significant application downtime (it requires a full BCP-out and BCP-in of the table). For this reason, some developers or DBAs have decided not to use identity columns at all.
In ASE 12.5.0.3 and 12.5.1, new features were implemented to address these issues, and as of these versions, there are no reasons anymore to avoid identity columns out of fear of identity gaps.


 
 This document is located at www.sypron.nl/idgaps.html