SEQUENCE–How to create Identity Key across tables in SQL Server “Denali” ?

SQL Server “Denali” introduces a new feature called “SEQUENCE”. SEQUENCE is a user-defined object that generates a sequence of numeric values according to specified options.

Its not bound to a table like IDENTITY, You can use SEQUENCE across tables

Supported Data Types for creating SEQUENCE are as follows:

  • TinyInt
  • SmallInt
  • Int
  • BigInt
  • Decimal
  • Numeric
  1. /* Create Sequence With Start Values */
  2. CREATE SEQUENCE RunningNumbers
  3.     START WITH 1
  4.     INCREMENT BY 1;
  5. GO


The above code creates a simple SEQUENCE with starting number as 1 and increments by 1

To get the next number in SEQUENCE, you need to use “NEXT VALUE” option

  1. SELECT (NEXT VALUE FOR RunningNumbers) AS [RunningNumbers];


The below code might syntactically look same as the SEQUENCE "- RunningNumbers, but if you look at the output, its entirely different

  1. /* Create Sequence Without Start Values */
  2. CREATE SEQUENCE RunningNumbers2
  3.     INCREMENT BY 1;
  4. GO

  1. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

If you see the output from RunningNumbers2, you will be getting a Negative value, its due to not specifying the Start number, so make sure you specify a Positive Start Number if you don’t want your table keys to Negative


Other options available in Creating Sequence are as follows:

  2. CREATE SEQUENCE RunningNumbers3
  3.    AS tinyint
  4.     START WITH 1
  5.     INCREMENT BY 1
  6.     MINVALUE 1
  7.     MAXVALUE 5
  8.     CYCLE ;
  9. GO


You can specify Minimum Value and Maximum Value and whether to Cycle the numbers when reaching maximum value or not


  1. SELECT NEXT VALUE FOR RunningNumbers3 AS ID, Name FROM sys.objects ;
  2. GO


If you see the below output, for every 5 records, the IDs are recycling and starts from 1 again


Restarting the Sequence

To restart the Sequence, you need to ALTER the sequence with RESTART option and start value

  1. /* Restart the Sequence */
  3. ALTER SEQUENCE [dbo].[RunningNumbers2]

  2. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

Since we restarted the Sequence with 1, Now we are getting output as 1 for RunningNumber2


Find the available Sequences in a database

  2.     object_id, name, type_desc, start_value, current_value, increment,
  3.     minimum_value, maximum_value, is_cycling, is_cached, cache_size
  4. FROM sys.sequences

You can Sys.Sequence system view to query the list of available Sequences in a database


How to use it as Identity across tables ?

Create tables for each reqion

  1. — Create tables
  2. CREATE TABLE Orders_West
  3.     (OrderID int PRIMARY KEY,
  4.     Name varchar(20) NOT NULL,
  5.     Qty int NOT NULL);
  6. GO
  9. CREATE TABLE Orders_East
  10.     (OrderID int PRIMARY KEY,
  11.     Name varchar(20) NOT NULL,
  12.     Qty int NOT NULL);
  13. GO
  15. CREATE TABLE Orders_South
  16.     (OrderID int PRIMARY KEY,
  17.     Name varchar(20) NOT NULL,
  18.     Qty int NOT NULL);
  19. GO
  21. CREATE TABLE Orders_North
  22.     (OrderID int PRIMARY KEY,
  23.     Name varchar(20) NOT NULL,
  24.     Qty int NOT NULL);
  25. GO


Insert data to table using Sequences

  1. — Insert Four records
  2. INSERT Orders_West (OrderID, Name, Qty)
  3.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Apples', 2) ;
  4. INSERT Orders_East (OrderID, Name, Qty)
  5.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Oranges', 1) ;
  6. INSERT Orders_South (OrderID, Name, Qty)
  7.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Grapes', 1) ;
  8. INSERT Orders_North (OrderID, Name, Qty)
  9.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Banana', 1) ;
  11. GO


Query the inserted data

  1. SELECT * FROM Orders_West
  2. UNION
  3. SELECT * FROM Orders_East
  4. UNION
  5. SELECT * FROM Orders_North
  6. UNION
  7. SELECT * FROM Orders_South


If you see the Union output from 4 different tables, we are able to create a identity column across 4 tables, This will be very helpful when you horizontally partition data across tables


Delete the Sequences

  1. DROP SEQUENCE RunningNumbers;
  2. DROP SEQUENCE RunningNumbers2;

I hope you all find this information about Sequences useful and informative !!!


Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: