Having worked with Oracle databases during some portion of the last decade, I was introduced to the Sequence Object. Since then, I had been especially missing  this feature in SQL Server. But my wait is over with its introduction in the release of SQL Server 2012!

A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table.

Before we explore the sequence object in more detail, here is a brief summary of the difference between identity and sequence.

• Sequence is Table independent while Identity is Table Specific
• Unlike Identity, you can obtain the new value from a Sequence before using it in a SQL Statement
• New value for a Sequence can be generated for both UPDATE as well as INSERT statements
• You can define the minimum & maximum values as well as cycling & cache size options for a Sequence
• Unlike Identity, a sequence object will not auto increment without any reference to it

You can create a sequence by using the T-SQL Create Sequence statement or in SQL Server Management Studio by navigating through the Object Explorer to Your Database > Programmability > Sequences, Right click and Create New Sequence.

Let’s look at a simple T-SQL statement for creating a basic sequence; 

 CREATE SEQUENCE dbo.demo_2012_sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;

Some of the interesting points to note here are;

• The MINVALUE and MAXVALUE should fall within the range of the data type of the sequence object
• The INCREMENT BY can be positive (makes an ascending sequence) or negative (makes a descending sequence), but not Zero (0)
• CYCLE – Specifies if the sequence should restart when the maximum value ( minimum value for a descending sequence) has been reached
• CACHE – Can improve performance by pre-allocating the number of sequence numbers specified, thus minimizing disk IOs. Note that an unexpected shutdown may result in the loss of values remaining in cache.

Once this sequence object has been created, you can use the Next Value For  function, to generate a sequence number from it. It is a non-deterministic function and can be used in stored procedures and triggers.  When using it in a query, please be aware of the limitations and restrictions on its usage; the most notable one being the restriction on using it in queries that contain SET Operators like UNION, UNION ALL, etc. It can also be used in a Default Constraint as well as an OVER ORDER BY Clause. A complete list of the restrictions and limitations on its usage is available here.

You can get one or more values out of the sequence object. You can also ALTER the sequence object to RESTART it, or change many of its properties. Let’s look at some examples.

• Example 1 – Get one value out of the Sequence Object at a time

-- get one value out of the sequence object at one time
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no;
GO
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no;
GO

And the results look like:

seq_no
-----------
1

(1 row(s) affected)

next_seq_no
-----------
2

 

(1 row(s) affected)

• Example 2- Restart the sequence and get multiple values out of the sequence object at one time

 -- creating a table and populate it with 5 rows
CREATE TABLE #demo_sequence
(demo_name VARCHAR(12));

INSERT INTO #demo_sequence
VALUES  ('row_1'),
        ('row_2'),
        ('row_3'),
        ('row_4'),
        ('row_5');
GO

-- Restart the sequence from 1
ALTER SEQUENCE dbo.demo_2012_sequence
    RESTART WITH 1
    INCREMENT BY 1;
GO

-- get 5 values out of the sequence object at 1 time
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no, demo_name FROM #demo_sequence;
GO

And the results look like:

seq_no      demo_name
----------- ------------
1           row_1
2           row_2
3           row_3
4           row_4
5           row_5

(5 row(s) affected)

You can also use the system object sys.sp_sequence_get_range to generate and return a number of values, along with some of the metadata related to the range.

The sys.sequences view can also be used to query metadata for a sequence object. It contains one row for each sequence object in the database.

 SELECT
        current_value,
        cache_size,
        is_cached,
        create_date,
        is_exhausted
FROM sys.sequences
WHERE name = 'demo_2012_sequence';
GO

And the results look like:

current_value   cache_size  is_cached   is_exhausted
--------------- ----------- ----------- ------------
5               20          1           0

(1 row(s) affected)

 In conclusion, the new Sequence Object can provide a viable alternative to Identity, and is definitely worth exploring for your next project.