Sequences in SQL Server

  • Article

Customers have been asking for sequences for a very long time and now finally it is included with SQL Server Denali CTP 1. You don't have to do workaround of sharing identity table across all tables now.

Creating a Sequence:

CREATE SEQUENCE counter

AS INT

MINVALUE 1

NO MAXVALUE

START WITH 1;

GO

Get Next Value from Sequence:

SELECT NEXT VALUE FOR counter

Using inside a Table

 CREATE TABLE t (id INT DEFAULT NEXT VALUE FOR counter)

Reset the Counter:  

ALTER SEQUENCE counter RESTART

 Create Sequence using SSMS:

Connect to SQL Server Denali using SSMS

Expand Databases node ->Programmability->Sequences folder as shown below

 

Launch the new sequence dialog from the context menu of Sequences which launches the following dialog

 

 Enter the name of the Sequence, Schema, datatype

Options:

Starting minimum value

Maximum value

Cycle: In order to repeat check this option

Click ok to create a sequence