The Release of SQL Server 2012 introduces the sequence object to the world of SQL Server. 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. A few interesting differences between the two are;

• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in a SQL Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a SQL object will increment its value only when it is explicitly called

A detailed list of differences between Identity and Sequence objects can be found here.

You can use the SQL Server Management Studio GUI or a TSQL statement to create a sequence object.

• For GUI, navigate through the object explorer window in SQL Server management Studio: Your Database > programmability > Sequences – Right Click – Create New Sequence.
• The TSQL statement for creating a basic sequence looks something like this;

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 aspects of the Create Sequence statement 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 the sequence object has been created, you can use the Next Value For  function, to generate a sequence value from it. It’s 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 from 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. Let’s look at an example;

DECLARE @range_first_value sql_variant , 
        @range_first_value_output sql_variant,
        @range_last_value_output sql_variant ;

EXEC sp_sequence_get_range
 @sequence_name = N'[dbo].[demo_2012_sequence]'
 , @range_size = 4
 , @range_first_value = @range_first_value_output OUTPUT
 , @range_last_value = @range_last_value_output OUTPUT;

SELECT @range_first_value_output AS RangeFirstNumber,
       @range_last_value_output AS RangeLastNumber;
GO

And the results look like:

RangeFirstNumber      RangeLastNumber
--------------------- ------------------
6                     9

(1 row(s) affected)

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, if you are looking for one. It is definitely worth exploring for your next project.

References

• Sequence Object in SQL Server - http://msdn.microsoft.com/en-us/library/ff878058.aspx
• Sequence Object in Oracle - http://docs.oracle.com/cd/B10500_01/server.920/a96521/views.htm#581
• Identity Property - http://msdn.microsoft.com/en-us/library/ms186775.aspx
• Differences between Sequence & Identity - http://www.sqlserver-training.com/what-is-the-difference-between-identity-and-sequence
• Code Examples - http://sqlwithsanil.com/