* 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
grant execute on sp_insert_invoice to public
go
/*
* Create the procedure to fix an identity gap.
* It takes one parameter, and can be called in two ways:
*
* 1. If "-1" is specified as a parameter, the keytable
* will be dropped and recreated according to the highest
* existing invoice number. The best use of this form
* is to always run this procedure directly after server
* startup, so that a possible identity gap is be corrected
* before new invoices are inserted which could cause problems.
*
* 2. If another value is specified, it must be invoice number
* generated just before the identity gap occurred. All invoices
* having a higher invoice numbers than this will be corrected
* downwards to create a consecutive range. The keytable will
* be recreated and adjusted accordingly.
* Example: suppose the following invoice numbers exist:
* (...)
* 10028
* 10029
* 10030
* 10031 <--\
* 5000002 <--- identity gap here
* 5000003
*
