Home > SQL Denali, SQL Server > SQL SERVER – Sequence object in SQL Server 2012

SQL SERVER – Sequence object in SQL Server 2012


Sequence object is a new feature included in SQL Server Denali CTP3. It is used to generate sequence number in the SQL Server.

Sequence object’s default datatype is bitint. The default value for the declared datatype will be assigned to sequence object which is created without start value. It is schema-bound unlike identity column in a table. We can use a sequence object across different tables in a database with unique  values.

Learn more about Sequence object in MSDN

Create Sequence object

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 } ]
    [ ; ]

Get Sequence object value

SELECT NEXT VALUE FOR [schema_name . ] sequence_name;

Alter Sequence object

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 } ]
    [ ; ]

Drop Sequence object

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ]    sequence_name } [ ,...n ]
 [ ; ]

Video demonstration for Sequence object (Watch and Learn)

Example Script

 Download Examples

Thanks for watching and Reading.

Advertisements
  1. No comments yet.
  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: