SQL SERVER - SEQUENCE in SQL SERVER 2012

SEQUENCE is user-defined object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables. It generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences are not associated with specific tables like identity columns. The relationship between sequences and tables is controlled by the application.
A sequence can be defined as any integer type. Also the following types are allowed: tinyint, smallint, int, bigint, decimal and numeric with a scale of 0. Any user-defined data type (alias type) that is based on one of the allowed types.
If no data type is provided, the bigint data type is used as the default.

There are different ways to create SEQUENCE, like:

1. SQL Server Management Studio
2. TSQL statement

Let us take a quick look to create a SEQUENCE using TSQL Statements. It can be created using CREATE SEQUENCE Syntax

---- Create a SEQUENCE object on schema "dbo" by the name of TEST_Sequence
CREATE SEQUENCE SEQ_MyTestSequence  
AS INT  
START WITH 1  
INCREMENT BY 1  
MINVALUE 0  
NO MAXVALUE  

---- Create table Customer
CREATE TABLE Customer
(
CustomerID  INT NOT NULL,
CustomerName VARCHAR(100)
)
GO

Now, Populate Customer table using SEQ_MyTestSequence to generate the CustomerId column:

INSERT INTO Customer(CustomerID, CustomerName)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, 'JOHN'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'ANTHONY'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'JUANA')

Lets us look at results, which we inserted using SEQUENCE

SELECT * FROM Customer

We should note that, sequence are global to a database and not dependent on tables. Let us create a new table named Account and use the same sequence which we used for Cutomer table in past to understand.

--- Create a new table called Account

CREATE TABLE Account(
[AccountID] INT NOT NULL,
[AccountNumber] [varchar](6) NULL
) 
GO

INSERT INTO Account(AccountID,AccountNumber)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, '000001'),
(NEXT VALUE FOR SEQ_MyTestSequence, '000002')
GO

SELECT * FROM Account

In above result, NEXT value of SEQUENCE has given a next number of SEQUENCE. Last number was 3 that was used in Customer)

Every time when we use NEXT VALUE FOR SEQ_MyTestSequence, it will give a new value. for example selecting or printing NEXT value of SEQUENCE Every time we will get a New Value

SELECT NEXT VALUE FOR SEQ_MyTestSequence 

Let us see how to check the current value of the SEQUENCE:

SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

With the help of following statement we can alter a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
RESTART WITH 101

In above code, we have altered a SEQUENCE and started with 101, let us check it by running the following code

SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

We can change the MAX and MIN value of a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
MINVALUE 101
MAXVALUE 1011
CYCLE

Above code will alter SEQUENCE and specify start as 101 and MAX as 1011, once the MAX Value is reached, SEQUENCE numbers will be restarted from MINVALUE.

Now, verify MAX value and CYCLE Clause

SELECT *,NEXT VALUE FOR SEQ_MyTestSequence as NextId
FROM Account 

We can drop a SEQUENCE with the help of following statement:

DROP SEQUENCE SEQ_MyTestSequence 

Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx