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




Related Articles :

3 Responses to “SEQUENCE in SQL Server 2012”

  1. Nice article , Except performance why I would use this function?

    any practical Scene in which i can see the usage.

    • One scenario that I can think of is to coordinate identity values across multiple tables. If you have a parent table with two child tables for example, you would normally insert the parent rec, return the identity value, then insert the child rec. However, if the only reason the parent table exists is to make sure the child IDs are unique, you could use a sequence instead and get rid of the overhead of the parent table.

      Not sure if this is practical or not, just the first thing that came to mind.

    • It is very useful in ORM based applications where you need to know what is the ID of saved entity before commiting the transaction.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |