How to Create Sequence in SQL Server 2012

Introduction

This article explains how to create a SQL Sequence in SQL Server 2012. A SQL Sequence is a new feature introduced in SQL Server 2012. Many people confuse SQL Sequences and Identities. An Identity is generally applied to an individual table.

A SQL Sequence is an individual object. Let's see in details.

What is SQL Sequence

A SQL Sequence is a database object that generates numbers in sequential order. SQL Sequences are available to all users of the database. SQL Sequences are created using SQL statements.

What is the need of a SQL Sequence?

A SQL Server sequence object generates a sequence of numbers just like an identity column in SQL tables. But the advantage of sequence numbers is the sequence number object is not limited to a single SQL table. There is no direct relation between tables, table identity columns and number sequences.

Create SQL Sequence in SQL Server 2012

The following is the syntax of a SQL Sequence:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

Example of Creating a SQL Sequence in SQL Server 2012

-- Create sequence of numbers starting from 1 with increment by 1
CREATE SEQUENCE MyFirstSequence
       START WITH 1
       INCREMENT BY 1;
GO

You can also create a sequence from SQL Server Management Studio.

The following is the procedure to create a sequence from SQL Server Management Studio:

1. Open SQL Server Management Studio

2. Click on your database

3. Expand the Programmability folder

4. You can see the Sequences folder

5. Right-click on the Sequences folder

6. Create Sequences

Please see the following screenshot:



How to get the value of a number from SQL Sequence

After having created a SQL Sequence, you can get the number value from your created sequence using the NEXT VALUE FOR function. The NEXT VALUE FOR will return the next sequential number, the same as in the above screenshot.

You can see in the following example:

-- Read from Sequence of Numbers using Next Value For Function

SELECT NEXT VALUE FOR dbo.MyFirstSequence

How to use sequence in Insert operation

You can use a sequence in an Insert Query. You can insert a record into a table with a sequence number from a SQL Sequence object as shown in the following code.

Create SQL table Employees

-- Create sql table Employees

CREATE TABLE Employees (

  EmployeeID int Identity(1,1) PRIMARY KEY,

  UserID int,

  FirstName nvarchar(100) NOT NULL,

  LarstName nvarchar(100) NOT NULL

)

 

-- Insert new rows into sql tables

INSERT INTO Employees (

  UserID, FirstName, LarstName

) VALUES (

  NEXT VALUE FOR MyFirstSequence, 'Keyur', 'Patel'

);