Creating Sequential Numbers in SQL Server

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we have 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 introduced in SQL Server 2012 and experience the difference.

SQL Server 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.

Syntax of SEQUENCE:

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

Lets now take a look at an example on how to use SEQUENCE in SQL Server.

1. Creating a sequence
2. Create a sample Item table
3. Use Sequence while inserting data
4. Query the table
5. Reset Sequence values
6. Alter or Delete Sequence values

Step 1: In this step, we will create a new object called ItemIDSequence that will start with a value of 1 and increment its value by 1.

CREATE SEQUENCE ItemIDSequence
       START WITH 1
       INCREMENT BY 1;

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

CREATE TABLE ITEM 
        ( 
          ITEMNO INT, 
          ITEMNAME VARCHAR(50)
        );

Step 3: In this step, we will insert data in the ITEM table using ItemIDSequence which will automatically insert and increment the values of ItemIDSequence by 1.

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');

Step 4: 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.

SELECT * FROM ITEM
 
--You will see the following result set.
 
ITEMNO	    ITEMNAME
====================
1	    MANGO
2	    APPLE
3	    BANANA
4	    ORANGE
5	    GRAPES

You can Alter, Delete or Query any sequence using the below examples:

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

Important and Best Practices for Sequence:

1. Sequence values are not automatically protected after insertion into a table. You should use Update Trigger on a table to prevent sequence values from being changed.
2. Sequence does not automatically enforce uniqueness for sequence values. You should create unique index on the sequence column to enforce uniqueness.
3. If you have created a sequence (Example: 1 through 100) and if the rows in the table grows beyond 100 rows, SQL Server would start assigning values 1 through 100 again.
4. By default, if you do not specify data type for a sequence, BIGINT data type is used. (Note: you can create sequence of any integer data type)