Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

SQL Server 2012 Sequence Numbers


By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Identities

Problem

Sequence Numbers are a new feature in SQL Server 2012.  Can you give me the details on this new feature?

Solution

Essentially a sequence number can be used in lieu of an IDENTITY column, but it also has some other interesting capabilities.  Books on Line describes a sequence number as "a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created".

In this tip I'll go through the details on sequence numbers, do a comparison with IDENTITY columns,  and provide some scenarios where it could be useful. 

Create Sequence

Let's start out by reviewing the CREATE SEQUENCE Transact-SQL statement.

CREATE SEQUENCE [schema_name . ] sequence_name
        [ <sequence_property_assignment> [ ,...n ] ]
    [ ; ]
<sequence_property_assignment>::=
{
    [ AS { built_in_integer_type | user-defined_integer_type } ]
    | START WITH <constant> 
        | INCREMENT BY <constant> 
        | { MINVALUE <constant> | NO MINVALUE }
        | { MAXVALUE <constant> | NO MAXVALUE }
        | { CYCLE | NO CYCLE }
        | { CACHE [<constant> ] | NO CACHE }
}

The following are the main points about the CREATE SEQUENCE statement:

  • Specify the schema where you want to create the sequence
  • Specify the name of the sequence
  • The sequence type can be any of the built-in integer types; e.g. tinyint, smallint, int, bigint, decimal or numeric; decimal or numeric require a scale of 0
  • The sequence type can also be a user-defined type that is based on one of the built-in integer types
  • The default sequence type is INT
  • Use START WITH <integer constant> to specify the first sequence number to be assigned; this can be a negative number
  • INCREMENT BY <integer constant> determines the next value assigned by the sequence number; this can be a positive or negative value but not 0
  • MINVALUE <integer constant> and MAXVALUE <integer constant> provide the bounds for the sequence number; the default for MINVALUE is 0 for a tinyint and the smallest negative number for the type of the sequence number; the default for MAXVALUE is the maximum value for the type of the sequence number
  • Specify CYCLE to restart the sequence number at the MINVALUE after the MAXVALUE is reached
  • Specify NO CYCLE to throw an exception after the MAXVALUE is reached rather than restarting with the MINVALUE
  • Use CACHE <integer constant> to retrieve a sequential block of sequence numbers

Use the NEXT VALUE FOR statement to assign and retrieve the next value for a sequence number.

Sequence Number Examples

The following examples will show the basic use cases for a sequence number.  The examples use the following schema and table:

CREATE SCHEMA mssqltips
GO
CREATE TABLE mssqltips.Tip (
  TipNumber INT PRIMARY KEY
, Title VARCHAR(50)
)
GO

Create a sequence number in the mssqltips schema to assign tip numbers on the MSSQLTips web site:

CREATE SEQUENCE mssqltips.TipNumber
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
GO

Get the next tip number and assign to a T-SQL variable (you could use @TipNumber in the VALUES clause of one or more INSERT statements):

DECLARE @TipNumber As INT = NEXT VALUE FOR mssqltips.TipNumber
PRINT @TipNumber

Insert a row into a table and get the next value for the TipNumber:

INSERT INTO mssqltips.Tip (TipNumber, Title)
VALUES (NEXT VALUE FOR mssqltips.TipNumber, 'What''s New in Denali? - Sequence Numbers')

Sequence Number Versus Identity Column 

A column in a table can have the IDENTITY property which provides for automatically assigning the next value to the column on insert.  Now that we have an idea of how to create and use a sequence number, let's compare and contrast the sequence number with the identity column.

  • An identity column is tied to a table; you have to insert a row in order to get the next value.  You can use the NEXT VALUE FOR statement to assign and retrieve the next value for a sequence number; it is not tied to a particular column in a table and you do not have to insert a row to get the next value.
  • A sequence number can be used to provide an automatically generated number that is used over multiple tables; the identity column is tied to a single table.
  • You can specify the starting value and increment with an identity column; with a sequence number you can specify the starting value, increment, minimum, maximum, caching, and whether to recycle the values
  • The NEXT VALUE FOR statement includes an OVER clause allowing you to assign sequence numbers based on an ORDER BY; an identity column does not have this capability.

Which One Should I Choose?

Choose a sequence number in these scenarios:

  • You want the next value without having to insert a row into a table.
  • You want to use the value across multiple tables.
  • You want to automatically recycle the values.

Choose an IDENTITY in these scenarios:

  • You want to assign the next value on insert for a single table.
  • You don't need to know the value before you perform the insert.

Miscellaneous Notes

  • To retrieve a range of values from a sequence number, use the sys.sp_sequence_get_range stored procedure.
  • To retrieve information about sequence numbers, use the sys.sequences stored procedure
  • Other related Transact-SQL commands are ALTER SEQUENCE and DROP SEQUENCE
  • The NEXT VALUE FOR statement is not run as part of the current transaction; a rollback does not affect the sequence number(s) already assigned.
Next Steps


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, April 18, 2011 - 12:29:04 PM - Vinay Back To Top

Sounds good, but this assumes that we are operating on a row by row basis and not dealing with heavey data loads, like bcp Identity keeps going good with the table, i am skeptical about this oracle feature in Sqlserver and developers writing more triggers and getting into a mess.

 


Thursday, March 31, 2011 - 1:53:07 PM - Kevin Back To Top

Sorry.  IE9 issue.

This is one of the few features of Oracle I miss.  Without it, I either need to lock a shared resource to pre-allocate a key, or use a GUID, which is too heavy-weight.


Learn more about SQL Server tools