T-SQL Puzzle – Custom Sequence
Itzik provides a T-SQL challenge involving a custom sequence generator.
January 25, 2009
There are certain cases where you need a sequence generator and the IDENTITY property is not an option. One such case is when your sequence must guarantee no gaps (e.g., with systems that do not allow gaps in invoice IDs). The IDENTITY property doesn’t guarantee no gaps since an increment of the identity value isn’t undone when the INSERT that caused it to increment fails. There may be other reasons why you would want to use an alternative sequence generator instead of the IDENTITY property. The T-SQL Puzzle involves creating such a custom sequence generator in SQL Server 2008.
Run the following code to create the Invoices and Sequence tables:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Invoices', 'U') IS NOT NULL
DROP TABLE dbo.Invoices;
GO
CREATE TABLE dbo.Invoices
(
invoiceid INT NOT NULL,
custid INT NOT NULL,
invdate DATE NOT NULL,
amt MONEY NOT NULL,
CONSTRAINT PK_Invoices PRIMARY KEY(invoiceid)
);