SEQUENCE in SQL Server 2012

You are no doubt used to IDENTITY and how this works with a seed and increment

value. SEQUENCE is structured in a very similar way, but with fewer limitations,
giving it a welcome flexibility.

A SEQUENCE object is created at the database level but, unlike an IDENTITY property,

it can be used across multiple tables. An IDENTITY value is generated when you
insert a row into a table and it cannot be updated. You can retrieve the SEQUENCE
value at any time and reset it without altering its previous value, and even set a
minimum and maximum value.

CREATE SEQUENCE mySequence AS int

START WITH 1
INCREMENT BY 1

We have not used the SEQUENCE object yet, so the first value returned should be 1.

Run the following statement to confirm this:

SELECT NEXT VALUE FOR mySequence AS [Next Value]

Result

NextValue

1

We can see that the SEQUENCE has not been used:

Next we will create a table so we can put SEQUENCE to the test.
Run the following code to create the Employee table:

CREATE TABLE Employee

(

EmployeeID int NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
)

Now we will insert a couple of rows. Note that in the following code we use NEXT

VALUE FOR just as we did in the preceding code to return the next SEQUENCE value.
This will increment the SEQUENCE, in our case by one, though you can set this to
be whatever you wish when you declare the SEQUENCE:

INSERT INTO Employee (EmployeeID, FirstName, LastName)

VALUES
(NEXT VALUE FOR mySequence, 'Rachel', 'Clements'),
(NEXT VALUE FOR mySequence, 'Jon', 'Reade')
GO

SELECT * FROM Employee

EmployeeID	FName	LName
2	Raj	K
3	Randheer	P

The SEQUENCE doesn't have to be unique; we can reset the seed to use the same

value again. If we had a unique constraint on our EmployeeID column we would
not be able to do this, but because we have not added a constraint, we can have
some fun.

Run the following statement:

ALTER SEQUENCE mySequence
RESTART WITH 1

If again insert the value by using it the new value would be 1.
If we wanted to set a minimum and maximum value we could have declared our
SEQUENCE as follows:
CREATE SEQUENCE mySequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 15

However we can change the maximum value using an ALTER statement.

If we wanted to restrict the number of rows inserted into a table we could use a

SEQUENCE object to limit this number. So as you can see, the seed and increment
values operate in the same fashion as IDENTITY, but you are free to use and
manipulate it.