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.

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!