Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Sequence Object in SQL Server 2012

By Sanil Mhatre,

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

Total article views: 7836 | Views in the last 30 days: 4
 
Related Articles
BLOG

Denali — Day 10: Sequence object

Denali – Day 10: Sequence object Sequence is an object introduced in Denali, this is similar to...

SCRIPT

New feature in SQL Server 2012 - Sequence

A new feature in SQL Server 2012 is Sequence. A Sequence object provides functionality similar to Id...

FORUM

Sequence of Database Objects Dependency - SQL 2005

Sequence of Database Objects Dependency - SQL 2005

FORUM

How to handle parallel objects sequence after conditional split for two different case

Conditional split and following parallel object sequence

ARTICLE

Creating Dynamic Outputs in SSIS

In this article, we'll see how you can use the Foreach loop and the Expression Language to create mu...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones