Managing several auto-increments
Managing several auto-increments on a single table, when one column has to be numbered within the scope of another.
How do you manage several auto-increments on a single table, when one column has to be numbered within the scope of another?
The classic example is a table holding a document number — an invoice number, say — numbered per branch office:
CREATE TABLE dbo.Invoice (
BranchNumber TINYINT NOT NULL,
InvoiceNumber INT NOT NULL,
CONSTRAINT pk_invoice PRIMARY KEY (InvoiceNumber, BranchNumber)
);
Here is a procedure that does it:
CREATE PROCEDURE [dbo].[AddInvoice]
@BranchNumber tinyint
AS BEGIN
SET NOCOUNT ON
DECLARE @new_id int;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRANSACTION;
SELECT @new_id = ISNULL(max(InvoiceNumber),0) + 1
FROM Invoice
WHERE BranchNumber = @BranchNumber ;
INSERT INTO Invoice (BranchNumber, InvoiceNumber)
VALUES (@BranchNumber, @new_id) ;
COMMIT TRAN
SELECT @new_id AS id
END TRY
BEGIN CATCH
ROLLBACK TRAN;
SELECT NULL AS id;
;THROW;
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN @new_id
END
GO
The SERIALIZABLE isolation level is what makes this correct: it takes a range lock on the rows for the branch being numbered, so two concurrent sessions cannot read the same maximum and allocate the same number.