From the point of view of a DBA or end-user, identity gaps are almost always inconvenient, and should often be fixed immediately.
It should be noted here that it is always possible that some individual identity column values are missing. This can happen when the transaction containing the insert operation is rolled back: the identity value that was already issued for this rolled-back row will not be re-used and therefore never show up as an invoice number.
In this article, an "identity gap" refers to a gap of a large number of units, and not to individual missing identity column values.
Why "identity gaps" occur
Identity gaps can occur following a rough server shutdown ("shutdown with nowait") or a crash of the server process. This is related to the algorithm the server uses to generate identity column values: essentially, a counter is kept in server memory, holding the identity value most recently issued. When a new row is inserted, this counter is incremented and the resulting value is assigned to the identity column in that row. While the new data row itself is written to disk, the new value of the in-memory counter is not. Only when the server is shut down in a normal way, this value is saved on disk.
This algorithm makes the identity feature very fast, because no I/O is required to generate a new value. On the other hand, should the value of the in-memory counter be lost, as is the case for a server crash or a "shutdown with nowait", it is not possible to continue at the next identity value, because the last issued value was not saved anywhere. In this case, the server will continue generating identity values starting at some much higher value - which is what creates the identity gap.
Exactly at which higher value the server picks up, is determined by the configuration parameter "identity burning set factor", which, to a certain extent, can be used to limit the maximum possible size of an identity gap. However, because this a server-wide setting, it is not possible to apply this to individual tables.
See the ASE System Administration Guide and Technical Document #20113 at http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=20113 for a detailed description of how to use this configuration parameter.
In view of these underlying technicalities, the risk of running into identity gaps could be seen as the price one has to pay for the high performance offered by the identity column feature. Also, it is clear that this risk cannot be completely excluded. DBA’s should be aware of this, and be prepared to perform recovery procedures.
Fixing "identity gaps" the slow, classical way
Let’s assume that, once an identity gap has occurred, it should be repaired as soon as possible. Basically, this involves the following two actions:
Updating those rows which have received very high values in their identity columns to the correct values that should have been generated instead. In the above example, 5000002 and 5000003 should be changed to 10032 and 10033, respectively.
Resetting the value of the identity column downwards, so that a correct value generated when the next row is inserted (10034 in the example).
Unfortunately, none of these actions can be performed directly. The DBA is not allowed to update the value of an identity column in an existing row, nor can the value of future identity values be modified downwards. Therefore, the only available procedure to fix a situation where identity gaps have occurred has been the following:
Switch on the identity_insert option for the invoices table with the following statement:
set identity_insert invoices on
This will allow an explicit value to be specified for the identity column in an insert statement. Note that this option can be enabled for only one table at a time.
For all rows with problematic 'high' identity values, delete the row and re-insert it with the proper value one would have liked to see there in the first place, effectively performing an update. In the example, this would update invoice numbers 5000002 and 5000003 to 10032 and 10033, respectively.
BCP the invoices table to a file, say invoices.bcp .
Drop and re-create the invoices table. Note that dropping the table will implicitly switch off the identity_insert option.
BCP the invoices.bcp file back into the invoices table using the BCP -E option (for identity insert).
Rebuild any indexes on the table, if applicable.
Applying this procedure to a multi-million row, real-life-size application table could well take a few hours, during which the invoicing application is unavailable. In many cases, end-users and management would pr
