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.