The “SQL Guy” Post # 29: Using SEQUENCE in SQL Server 2012

  • Article

Before joining Microsoft I spent many years working as an Oracle DBA, SQL Server DBA, consultant, trainer, and so on. I got to see the differences in features and functionality between a wide variety of products. I also saw that there was a different approach that Oracle and SQL Server used for incremental numbers in databases. 

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

 

SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.

 

Here’s the syntax on how you can create a SEQUENCE using various options.

 

CREATE SEQUENCE SYNTAX

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

    [ ; ]

 

Let’s now take a look at an example on how to use SEQUENCE in SQL Server 2012.

 

In the below example, we will look at:

§ Creating a sequence

§ Create a sample Item table

§ Use SEQUENCE when inserting data in a table

§ Query Item table

§ Alter or delete SEQUENCE from SQL Server

 

In this step, we will create a new object called MYSEQUENCE1 that will start with a value of 1 and increment it’s value by 1.

 

STEP 1 - CREATING A SEQUENCE CALLED MYSEQUENCE1

 

CREATE SEQUENCE ITEMIDSEQUENCE

      START WITH 1

      INCREMENT BY 1;

 

 

In this step, we will create a sample ITEM table with just two columns.

 

STEP 2 - CREATING ITEM TABLE

 

CREATE TABLE ITEM

(

ITEMNO INT,

ITEMNAME VARCHAR(50)

);

GO

 

 

In this step, we will insert data in the ITEM table by leveraging the newly create MYSEQUENCE1 which will automatically insert and increment the values of MYSEQUENCE1 by 1.

 

STEP 3 – USING SEQUENCE FOR INSERTING DATA IN A TABLE

 

INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'MANGO');

INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'APPLE');

INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'BANANA');

INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'ORANGE');

INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'GRAPES');

GO

 

 

In this step, we will query the ITEM table to review the data in the ITEM table. Notice that the ITEMNO column in the ITEM table has sequential numbers that were auto-inserted using ITEMIDSEQUENCE sequence.

 

STEP 4 – QUERYING DATA FROM A TABLE USING SEQUENCE

 

SELECT * FROM ITEM

 

YOU’LL SEEE THE FOLLOWING RESULT SET.

 

ITEMNO

ITEMNAME

1

MANGO

2

APPLE

3

BANANA

4

ORANGE

5

GRAPES

 

 

 

In this step, we will look at Altering, Deleting & Querying Sequences:

 

ALTER, DELETE, AND QUERY SEQUENCES

 

To delete a sequence:

DROP SEQUENCE ITEMIDSEQUENCE

 

To alter a sequence:

ALTER SEQUENCE ITEMIDSEQUENCE

      RESTART WITH 900

      INCREMENT BY 1

  GO

 

To query the next value of Sequence for a particular SEQUENCE object:

SELECT NEXT VALUE FOR ITEMIDSEQUENCE

 

To query all sequences available in a database, you can use the below query:

SELECT * FROM SYS.SEQUENCES

 

 

 

 

THINGS TO KEEP IN MIND:

 

§ Sequence values are not automatically protected after insertion in to a table. You should use Update Trigger on the table to prevent sequence values from being changed.

 

§ Sequences does not automatically enforce uniqueness for sequence values. You should create unique index on the Sequence column to enforce uniqueness.

 

§ If you have created a sequence for example assigns values 1 through 100, and if the rows in the table grows beyond 100 rows, then Sequence would start assigning values 1 through 100 again.

 

§ By default, if you do not specify data type for a sequence, BIGINT data type is used. Keep in mind that you can create sequence that are of any integer data type.

 

 

 DamirB-BlogSignature