SQL 2012 : SEQUENCE Walkthrough

SQL 2012 introduces a SEQUENCE (apparently Oracle has had this for a while).
You create a SEQUENCE like this -

CREATE SEQUENCE [TestSequence]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
GO

Retrieving values from the sequence works like this -

SELECT NEXT VALUE FOR [TestSequence]

First Run : 1 
Second Run : 2 
Third Run : 3 

The Forth Run produces the following error, as the sequence had reached it's maximum value of 3 -

Msg 11728, Level 16, State 1, Line 1 The sequence object 'TestSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. 

By default, a sequence does not repeat itself.
You could restart the sequence...

ALTER SEQUENCE [TestSequence] RESTART

Or add the keyword CYCLE to the original statement...

DROP SEQUENCE [TestSequence]
GO

CREATE SEQUENCE [TestSequence]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
CYCLE
GO

Having done so, we'll run the statement again -

SELECT NEXT VALUE FOR [TestSequence]
GO

First Run : 1 
Second Run : 2 
Third Run : 3 
Forth Run : -2147483648 
Fifth Run : -2147483647 
Sixth Run : -2147483646

So without specifying a minimum value, the SEQUENCE uses the minimum value for the INT data type and continues cycling from there.

Let's try again.

DROP SEQUENCE [TestSequenceCycle]
GO

CREATE SEQUENCE [TestSequenceCycle]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
MINVALUE 1
CYCLE
GO

SELECT NEXT VALUE FOR [TestSequenceCycle]
GO

First Run : 1 
Second Run : 2 
Third Run : 3 
Forth Run : 1 
Fifth Run : 2 
Sixth Run : 3 

The most obvious use for the sequence is populating several child tables with a key value from a parent. This saves us looking up an inserted IDENTITY value.