Creating Unique Ids

I watched the Dat323 screencast from Teched 97 by Itzik Ben-Gan. This is definitely in my top ten of screencast. The screencast isolated 3 topics and stepped through the logic of each.

Creating unique Id's
==============
There are two types op Id's: either all the ids must always exist or missing id's are allowed. A good example of all id's must exist is invoices, while a booking number may allow for missing ids.

The method use blocking for all id's and non-blocking algorithm for missing id's.

Blocking Algorothm:-
================
CREATE TABLE dbo.SyncSeq(val INT);
INSERT INTO dbo.SyncSeq VALUES(0);
GO
CREATE PROC dbo.usp_SyncSeq
@val AS INT OUTPUT
AS
UPDATE dbo.SyncSeq
SET @val = val = val + 1;
GO

Non-Blocking Algorithm
=================
The trick here is to limit the number of rows in this table. Thus the Save allows the rollback, but still increase the increment.

CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
CREATE PROC dbo.usp_AsyncSeq
@val AS INT OUTPUT
AS
BEGIN TRAN
SAVE TRAN S1;
INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
SET @val = SCOPE_IDENTITY()
ROLLBACK TRAN S1;
COMMIT TRAN
GO