Big Data, Data Analytics and Data Science | BI-BIGDATA.com

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.

Thanks
Manish