Understanding Sequences in SQL Server 2012 (Denali) - Part 1

by Rohit 9. April 2012 14:17

This is a two-part series on Sequences, an enhancement newly introduced in SQL Server 2012 (Denali). In this post I introduce you to SQL Server Sequences and how internally it works. In the second part of this series, I will cover the usage of sequences in depth.

Sequences are not new in the database world. Oracle, PostgreSQL and other databases have it from a long time but SQL Server added it quite late. It is now available with SQL Server 2012 (code named "Denali"). 

In this post I am going to show you:

  • What is a Sequence (in SQL Server) and how it differs from IDENTITY property?
  • What are the advantages and disadvantages of a Sequence?
  • Basic syntax.

 What is a Sequence (in SQL Server) and how it differs from IDENTITY property?

Conceptually, Sequences work in a similar manner as IDENTITY, i.e., they generate a sequence of numeric values automatically. But what makes a Sequence different from IDENTITY  is that a Sequence is an independent object like other database objects as opposed to the IDENTITY which is tied to a particular column of a table. A single database can make use of multiple Sequences.

Sequences generate numeric values in an ascending or descending order at a defined interval and may cycle as requested. An interesting feature of a Sequence is that an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. Another added advantage is that while creating a Sequence, you don't even have to specify which database is going to use it.

Advantages: 

  1. Sequence offers better performance and no overhead when  compared to IDENTITY          property. IDENTITY uses a built-in caching mechanism with which only 10 values are pre-cached in memory. As more values are needed, the background process generates more values. The small 10 value pool can run out of value soon and it creates an overhead for the background process.  Whereas, with a Sequence, you can set the cache limit manually however it is recommended that you do not set the cache limit too high and keep it to an optimum level. 
  2. Can be recycled once it reaches the MAX VALUE.
  3. You can assign a START WITH value if you don’t want the sequence to start from the MIN VALUE.
  4. You can obtain the next value in the sequence before performing an INSERT using NEXT VAL.
  5. You can insert any unique value in the Primary Key column without setting SET IDENTITY INSERT ON/OFF.
  6. You can share sequence values across multiple tables in a database or across multiple columns in a table.
  7. You can assign multiple numbers at the same time to your application by retrieving the range using sp_sequence_get_range.


Disadvantages:

  1. Sequences cannot be used in sub-queries, CTE and with CHECK constraints. They cannot be used in Views as well.
  2. You cannot simply issue a DROP SEQUENCE statement if the sequence is being referenced anywhere in tables.
  3. Unlike IDENTITY values, the sequence values are not protected by themselves. To protect sequence values from being changed, an update trigger is needed to rollback changes.
  4. Sequence numbers in a table can have gaps when a transaction is rolled back or when a sequence is shared across multiple tables.
  5. If you assigned a CACHE value to a sequence then you need to be cautious at times of unexpected shutdown caused by power failure etc. In these scenarios, CACHE value is lost.

 

Basic Syntax

 

CREATE SEQUENCE Schema.SequenceName
    AS int
    INCREMENT BY 1 ;

 

CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

 

CREATE SEQUENCE Schema.SequenceName AS int
 START WITH 1
 INCREMENT BY 1
 MINVALUE 0
 NO MAXVALUE

 

Image courtesy of Club Oracle Community

Pingbacks and trackbacks (1)+

Add comment

biuquote
  • Comment
  • Preview
Loading

About Rohit Prakash

Software Craftsman @ Fortune 100 Company. Technical Reviewer of few programming books.

A Single, Writer, Reader and Adventurist.

You can reach me at:

rohit [at] irohitable.com

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------

Month List

Protected by Copyscape Web Plagiarism Software