Sql – How would you implement sequences in Microsoft SQL Server

databasesequencessqlsql-server

Does anyone have a good way of implementing something like a sequence in SQL server?

Sometimes you just don't want to use a GUID, besides the fact that they are ugly as heck. Maybe the sequence you want isn't numeric? Besides, inserting a row and then asking the DB what the number is just seems so hackish.

Best Solution

Sql Server 2012 has introduced SEQUENCE objects, which allow you to generate sequential numeric values not associated with any table.

Creating them are easy:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

An example of using them before insertion:

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
  VALUES (@NextID, 'Rim', 2) ;

See my blog for an in-depth look at how to use sequences:

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/