You are here:Home » SQL Server General » SEQUENCE Introduction in SQLServer 2011

SEQUENCE Introduction in SQLServer 2011

It is very hardly when we need to retain sequence globally. We can use identity property of column but it is limited to that table only.

SQL Server 2011 has introduced new feature named "SEQUENCE". We can retain the global sequence and can be used in multiple tables or in any queries. It's really global sequence generator and alternative option of Identity columns.

Let's see the simple example using it.

Creating database and table objests.

-- Creating database

CREATE DATABASE SEQUENCEDB
GO

USE SEQUENCEDB
GO


-- Creating table

IF( OBJECT_ID('SequenceTab') > 0 )
  DROP TABLE SequenceTab

GO

CREATE TABLE SequenceTab
  (
     Seq_Id INT,
     Name   VARCHAR(20)
  )

GO 

Now we will create a Sequence.

-- Creating Sequence

IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence')
DROP SEQUENCE NewSequence;
GO

CREATE SEQUENCE [NewSequence] AS INT
START with 1
INCREMENT by 1
MAXVALUE 1000
GO

You can create sequence in Object Explorer,



Let us insert some records in table using sequence and see the data in table.
-- Inserting records using Sequence

INSERT INTO SequenceTab
SELECT next value for [NewSequence], 'Sequence-1'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-2'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-3'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-4'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-5'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-6'
GO

-- Records in table 

SELECT Seq_Id,Name FROM SequenceTab
GO




Now we will alter the sequence and reset to 1 and then review the next sequence to be generated.

-- Reset the sequence , starting from 1

ALTER SEQUENCE [NewSequence]
RESTART WITH 1
GO


Let's see the next values from sequence from first run.

SELECT
next value for [NewSequence] as NeSequence
,*
FROM SYS.DATABASES
GO


Next values for sequence from second run.
SELECT 
next value for [NewSequence] as NeSequence
,* FROM SYS.DATABASES

GO












Happy sequencing!!