Even though this will hopefully remain a rare incident, there is always a possibility that such a repair procedure may have to be performed, because the risk of identity gaps just cannot be fully excluded. For this reason, DBA’s or developers sometimes choose not to use identity columns at all for applications with high availability requirements, because this could lead to unacceptable application downtime.
The above is not a far-fetched or hypothetical scenario: with a certain regularity, cries for help are posted in the Usenet newsgroup comp.databases.sybase by DBA’s suddenly facing an identity gap and who are desperate for a quick solution. Unfortunately, Sybase has not felt it necessary to implement additional functionality for making the process of fixing identity gaps easier, leaving DBA’s with nothing but the rather clumsy procedure described above.
A better way of repairing identity gaps
We will now look at a database design approach that allows DBA's to fix identity gaps quickly, in a matter of seconds or, in the very worst case, minutes.
The first step to achieve this is to use two database tables instead of one: the application table invoices plus a separate keytable named invoices_keytable.
create table invoices_keytable
(dummy_key numeric(10,0) identity)
create table invoices
(invoice_nr numeric(10,0),
customer_nr int,
amount money)
Note that the invoice_nr column is no longer an identity column, but a ordinary column of datatype numeric. The identity column has moved to table invoices_keytable , which contains just this one column and nothing else. The purpose of this identity column, named dummy_key , still is to generate key values for new invoices, but in a slightly different way than before.
When creating a new invoice, first a new invoice number is generated by inserting an "empty" row into invoices_keytable . The identity value assigned to the dummy_key column in this row is then automatically available through the global, session-specific variable @@identity . This invoice number is then used to insert the actual new invoice data into the invoices table:
/* insert an "empty" row to generate new invoice number */
insert invoices_keytable values ()
/* use identity value as key value for new row */
insert invoices (invoice_nr, customer_nr, amount)
values (@@identity, @new_customer, @new_amount)
This two-step way of inserting a new invoice is functionally identical to the "classical" situation where the invoice_nr column in the invoices table would have the identity property. Also, identity gaps can still occur in this design, with the same consequences for the application as before. However, once this happens, this new approach offers a much better way to repair the identity gap. A DBA should then take the following steps:
Update the invoices table using a normal update statement:
update invoices
set invoice_nr = 10032
where invoice_nr = 5000002
Contrary to the "classical" approach, this update will work because the invoice_nr column is not an identity column, but a normal column (NB: a similar statement is required for correcting invoice 5000003).
Drop and re-create invoices_keytable . No data is lost here, because the data rows in this table do not contain any useful information.
Reset the identity column value in invoices_keytable with the following statements:
set identity_insert invoices_keytable on
insert invoices_keytable (dummy_key) values (10033)
set identity_insert invoices_keytable off
The effect of these statements is that for the next row that will be inserted, the identity value generated will be 10034, which is exactly what the next invoice number should be. This is because the statement set identity_insert ... on allows an explicit identity value to be inserted in dummy_key column of invoices_keytable . If this value is higher than the highest identity value issued, the identity value is adjusted upwards. Because this mechanism doesn’t work in a downward direction, the table must be re-created first to make this trick work.
Of these three steps, the last two will always be very fast; these should take no more than a few seconds. The first step (updating the invoices table) should normally not take much time either; in case there are many invoice numbers that need to be corrected, this should still not take longer than a few minutes in the worst case.
The obvious way of implementing this reparat
