SEQUENCE in SQL Server 2012

Restarting a Sequence

Can we restart Sequence in SQL Server 2012? Of course you can. In identity you previously used Reseed for this.

ALTER SEQUENCE dbo.EmployeeSeq
RESTART WITH 2;

Above statement will allow you to restart the sequence from 2.

Sequence Transactions

Another issue is, what will happen to the next number of a sequence when the transaction is rolled backed.

Let us check this. First we will see what is the current value of  the sequence.

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Since, we have restarted the sequence this will return a value of 2 and now let us execute this in transaction and later we will rollback the transaction.

BEGIN TRAN
INSERT INTO Employee
(ID,Name)

VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

ROLLBACK TRAN

Again, we will check the next value for the sequence:

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Above query will return 7 which means the rollback statement does not rollback the next value for the sequence. This behaviour is the same as Identity.

CYCLE

 If you have set the cycle option on, your Sequence object will re-use numbers. Let us see this in an example. By stating CYCLE your sequence cycle option will be set to true.

CREATE SEQUENCE [dbo].[EmployeeSeq]
 AS [tinyint]
 START WITH 2
 INCREMENT BY 5
 CYCLE

 To see this option to work let us execute following statement.

SELECT
NEXT VALUE FOR EmployeeSeq
GO 100

Following image is a part of results you would get and you can see that after 255 it has restarted to 0 again.

OVER

Let us create a new sequence.

CREATE SEQUENCE SeqOrder AS tinyint
                START WITH 1
                INCREMENT BY 1
                MINVALUE 1
                NO MAXVALUE
                CYCLE;
GO

Now, let us run following query:

SELECT ID,Name,
NEXT VALUE FOR SeqOrder OVER (ORDER BY Name DESC) As [Order]
FROM Employee;

The results should be as shown below:

This means you can use Sequence as a running number in a query.