Proactive reparation of identity gaps Designing a database to allow quick reparation of identity gaps is a major improvement compared to the "classical" situation. Still, it might be preferable to ensure identity gaps will never strike an operational application at any time. This can be achieved by always running a program directly after server startup, which performs the following actions:
It inserts an "empty" row in invoices_keytable to obtain the next identity value through @@identity .
It retrieves the highest existing key value from the invoices table; hopefully, there would be an index to support this query.
It then compares these two values. If everything is normal, then the difference between these two values is not more than a few units (small gaps can always exist because individual insert operations can have been rolled back). If the difference is bigger than, say 100 units, this means an identity gap exists. A reparation procedure could then be run automatically, which drops and recreates the invoices_keytable.
Note that this check will cause one invoice number value to be missing from invoices , in case no identity gap exists. If this is not desirable, a variation on this procedure is to always rebuild the invoices_keytable, using the highest invoice number from invoices. Assuming these actions are performed directly after server startup, and before any applications are using the database, an identity gap (if present) will not yet have affected the values in the invoices table because no new invoices have been inserted yet. The situation will now be corrected immediately before wrong invoice numbers are generated. This ensures that identity gaps do not get a chance to go unnoticed until the first serious application problems start to appear. The downloadable example stored procedure mentioned earlier also works for this situation.
Conclusion It is possible to avoid the problems caused by identity gaps, the risk of which is implied when identity columns are used. Using the two-table design technique described in this article, identity gaps can be repaired quickly and even automatically, in contrast with the much more inefficient classical approach. This results in a significant improvement in application availability at a negligible performance cost.
[color=#DC143C]存储过程sp_fix_idgap.sql[/color]
/* * This script contains everything you need to run a demo of how * to use a two-table database design technique that allows * identity gaps to be fixed quickly using a stored procedure. * This script set up the following objects: * * - a table 'invoices', holding application data * - a table 'invoices_keytable', for generating invoice numbers * - a procedure 'sp_fix_idgap_invoices' to repair identity gaps * in the 'invoices' table * - a procedure 'sp_insert_invoice' to simulate the application * inserting invoices * / if not exists(select * from master.dbo.sysdatabases where name = "my_db" begin print "***************************************************" print "*** You should edit this script first ! ***" print "*** Change 'my_db' to the name of your database ***" print "*** in which you want to run this script. ***" print "***************************************************" end go
use my_db go
/* * create the application table holding invoice data */ if object_id("invoices" != NULL begin drop table invoices end go
create table invoices (invoice_nr numeric(10,0), customer_nr int, amount money) go create unique index ix1 on invoices(invoice_nr) go grant all on invoices to public go
/* * Create a separate procedure for creating the keytable. * This is required because it's not allowed to drop * and recreate an object with the same name in one procedure */ if object_id("invoices_keytable" != NULL begin drop table invoices_keytable end go
if object_id("sp_create_invoices_keytable" != NULL begin drop procedure sp_create_invoices_keytable end go
create procedure sp_create_invoices_keytable /* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */ as begin create table invoices_keytable (dummy_key numeric(10,0) identity)
/* * Partitioning only works in system 11.0 and later * The number of partitions can be increased if there * will be many concurrent inserts. */ alter table invoices_keytable partition 10 end go grant execute on sp_create_invoices_keytable to public go
/* * create the keytable */ exec sp_create_invoices_keytable go
/* * create a procedure to simulate application activity: * first generate a new invoice number, then insert a new invoice */ if object_id("sp_insert_invoice" != NULL begin drop procedure sp_insert_invoice end go
create procedure sp_insert_invoice /* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */ @p_customer_nr int, @p_amount money as begin insert invoices_keytable values () insert invoices values (@@identity, @p_customer_nr, @p_amount) end go gran