Denali T-SQL: Sequences, eh?

A new feature introduced in SQL Server Denali is Sequences which are another method of autonumbering. These have been around in Oracle for a while and at first, I was a bit unsure as to what their benefits would be, as we already have the IDENTITY property in SQL Server.

However, after giving it a bit of thought I can see areas where this would definitely be useful and in this post, Aaron Bertrand provides evidence of performance benefits over IDENTITY.

Consider a scenario where there Items are added to a database using Service Broker. The process is as follows:
1) Items are stored with a unique INT id
2) Client adds sends Items to the Application
3) Client receives confirmation that the items will be added along with referenceIds for each item
4) Item is added to the database via Service Broker

The issue here is with step 3 as we are unable to get the next available Identity without hitting the Item table and using something such as SCOPE_IDENTITY() to get the next value. This though doesn't fit the asynchronous approach.

Using Sequences allows us to get the Ids before processing the items and return the references to the client:

-- create a sequence
CREATE SEQUENCE dbo.ItemIDs    
AS INT    
   MINVALUE 1    
   NO MAXVALUE    
   START WITH 1;
GO

-- and a procedure to assign ids to the items which can be returned to the client
CREATE PROCEDURE dbo.GetItemIds
@Items XML
AS

DECLARE @TblItems TABLE (ItemID INT, Item NVARCHAR(255))

INSERT INTO @TblItems
SELECT NEXT VALUE FOR dbo.ItemIDs AS ItemID, tbl.cols.value('.', 'nvarchar(255)') AS Item
FROM @Items.nodes('//Item') AS tbl(cols)

-- i'll return this as XML and it can be passed to service broker
SELECT * FROM @TblItems
GO

DECLARE @Items XML = '<Item>Watch</Item><Item>Glasses</Item><Item>Ring</Item>'

EXEC dbo.GetItemIds @Items

GO

Ok, so it may be a contrived example but I hope it illustrates the idea. A further example may be a multipage web registration form which adds data to multiple tables and you don't actually have to commit the INSERTs until the very end. Sequences would help here too.