Advertisements
Home > Business Intelligence, Denali, SQL Server > Sequence Vs Identity – SQL Server 2012

Sequence Vs Identity – SQL Server 2012

What is sequence?

Sequence is a newly introduced database object in Sql Server 2012, Sequence is used to generate a sequence of numeric values in either ascending or descending order, along with an option to have the minimum and maximum values and also allows to cycle and cache for better performance.

The following is the syntax:

CREATE SEQUENCE [schema_name . ] sequence_name
 [ AS [ built_in_integer_type | user-defined_integer_type ] ]
 [ START WITH  ]
 [ INCREMENT BY  ]
 [ { MINVALUE [  ] } | { NO MINVALUE } ]
 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [  ] } | { NO CACHE } ]
 [ ; ]
  • Start with– the initial value to start with sequence.
  • Increment by – the step by which the values will get incremented or decremented.
  • Minvalue– the minimum value of the sequence.
  • Maxvalue-the maximum value of the sequence.
  • Cycle / No Cycle – to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
  • Cache / No Cache – to pre-allocate the number of sequences specified by the given value.

What is Identity?

Identity is a column attribute that has been available in all versions of SQL Server and many people mistakenly assume that Sequence is similar to Identity and will be the replacement of Identity in SQL Server 2012, however this is not true. Both of them are having their own usage, significance, functionalities and limitations. They both will continue to exists in  SQL Server 2012.

Illustration :-

Example 1:

-- CREATE A SEQUENCE WITH INITIAL VALUE 100 AND INCREMENT BY 1
CREATE SEQUENCE [DBO].[DEMOSEQUENCE] AS [BIGINT]
 START WITH 100
 INCREMENT BY 1

-- USE SEQUENCE IN A INSERT STATEMENT
CREATE TABLE DEMOTABLE(ID INT,INFO VARCHAR(100))
GO
-- INSERT ROWS IN THE DEMOTABLE
INSERT INTO DEMOTABLE VALUES(NEXT VALUE FOR DEMOSEQUENCE,'FIRST')
INSERT INTO DEMOTABLE VALUES(NEXT VALUE FOR DEMOSEQUENCE,'SECOND')
GO
-- CONFIRM THE RECORDS
SELECT * FROM DEMOTABLE

Output :

Example 2:

-- CREATE A SEQUENCE WITH INITIAL VALUE 100 AND INCREMENT BY 1
CREATE SEQUENCE [DBO].[DEMOSEQ] AS [BIGINT]
 START WITH 100
 INCREMENT BY 1

-- USE SEQUENCE AS DEFAULT WHILE CREATING A TABLE
CREATE TABLE DEMOTABLETEST(ID INT DEFAULT(NEXT VALUE FOR DEMOSEQ),INFO VARCHAR(100))
GO
-- INSERT ROW IN THE DEMOTABLE0
INSERT INTO DEMOTABLETEST (INFO) VALUES('SEQUENCE DEMO')
GO
-- CONFIRM THE RECORD
SELECT * FROM DEMOTABLETEST

Output:

To fetch the current value of sequence

-- TO FETCH THE CURRENT VALUE OF SEQUENCE
SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME=' DEMOSEQUENCE '

To fetch the next value of sequence

-- TO FETCH THE NEXT VALUE OF SEQUENCE
SELECT NEXT VALUE FOR DEMOSEQUENCE

The following is a comparative study about identity and Sequences.

No   Identity Sequence

1

Usage Identity will spawn unique number in a table. Sequence will spawn unique number in a database.

2

Type An Identity is an attribute for a column. A sequence is a database object.

3

Behavior Generates values when rows are inserted. An apps or SQL Server can fetch the next sequence without inserting the row using NEXT VALUE FOR function.

4

Instances Only one identity column per table is allowed. You can have more than one sequence on a table.

5

Dependency Identity is specific or limited to a single table. Sequences are independent of tables.

6

Speed Identity is slower. Sequence is significantly faster as it reads from memory rather than from the disk.

7

Next value Identity does not allow retrieving the next value. Using NEXT VALUE FOR function one can retrieve next value.
SELECT NEXT VALUE FOR DEMOSEQ

8

Current value Following ways to get the current value of an identity column.
1.  DBCC CHECKIDENT (‘DemoId’);
2.  Select @@IDENTITY3.  SELECT SCOPE_IDENTITY()4.  SELECT IDENT_CURRENT(‘DEMOID’);
To get the current value of a Sequence.SELECT CURRENT_VALUE FROM SYS.SEQUENCES WHERE NAME=’DEMOSEQ’

9

Syntax IDENTITY [ (seed , increment ) ]One can set only Seed value (Initial value) and value to Increment. CREATE SEQUENCE [schema_name . ] sequence_name
[ 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 } ]
[ ; ]One can set minvalue, maxvalue , and can opt to cycle and cache.

10

Update We can change the seed value using the following syntax, but can not modify the increment value.One canDBCC CHECKIDENT
(
table_name
[, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
Except the data type, we can alter other properties using ALTER SEQUENCE command.ALTER SEQUENCE [schema_name. ] sequence_name
[ RESTART [ WITH <constant> ] ]
[ INCREMENT BY <constant> ]
[ { MINVALUE <constant> } | { NO MINVALUE } ]
[ { MAXVALUE <constant> } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

11

System catalog All information about identity is available in sys.identity_columns All information about sequence is available in sys.sequences

Thanks
Manish

Advertisements
  1. RadhikaM
    June 6, 2012 at 4:55 pm

    Interesting post ! Wish they had introduced sequence in sql 2008 , could really start using it !

    • Manish Kaushik
      June 7, 2012 at 8:43 pm

      Thanks Radhika, and yes Sequences have been requested by the SQL Server community for years and finally in SQL Server 2012 we got this feature.

  2. Ashish Tiwari
    June 6, 2012 at 7:22 pm

    Very short and crisp explaination. Thanks for posting.
    However, I am wondering if we can use sequences across multiple databases of a server?

    Cheers,
    Ashish

    • Manish Kaushik
      June 6, 2012 at 7:54 pm

      Hi Ashish,
      Thanks for the felicitation.
      Yes you can use Sequence across database by using fully qualified name ( DatabaseName.Schema.SequenceName).
      Example :

      --VERIFY SEQUENCE EXISTS IN YOUR FIRST DATABASE
      USE TESTDB
      SELECT * FROM SYS.SEQUENCES WHERE NAME='DEMOSEQ'
      
      --USE THE SEQUENCE OF FIRST DATABASE INTO SECOND DATABASE 
      USE TEST
      --ACCESS METHOD 1:  
      SELECT NEXT VALUE FOR TESTDB.DBO.DEMOSEQ
      
      --ACCESS METHOD 1: 
      CREATE TABLE XSS(ID INT,MM CHAR(10))
      GO
      INSERT INTO XSS VALUES(NEXT VALUE FOR TESTDB.DBO.DEMOSEQ,'AA')
      GO
      

      Thanks
      Manish

  3. Samir kazi
    September 23, 2012 at 4:38 pm

    nicely done and explained!!!

  4. Samir kazi
    September 23, 2012 at 4:40 pm

    Really looking forward for he migration from oracle to SQL server 2012.. have to face lot of sequences enerated errors but this has release has overcome this…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: