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
[ INCREMENT
BY
[ {
MINVALUE [
[ {
MAXVALUE [
[ CYCLE | {
NO CYCLE } ]
[ { 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.
|
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.
credits to Saleem Hakani. my SQL Hero.
|
