SQL Server Portal

Microsoft launched many new features in the SQL server 2012 and one of its best features is SEQUENCE Object.
Let me explain it with a real world example:

It generates a sequence (Auto number) without associating with the table globally. (In earlier versions, we had to generate sequence manually).
The sequence should be of numeric data type (tinyint, smallint, int, bigint, decimal and numeric (scale should be zero)) and it can be generated in an ascending or descending order at a particular interval and we can also define the (cycle) to restart the sequence.

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 } ]
[ ; ]
But, the question is if we have an identity property, then why do we need sequence? Given below is an example that will demonstrate the importance of sequence object.
Example :
We developed a ticketing system in one of the service industries and the requirement was that we had to create tickets for multiple services.
And the ticket should not be repeated in any service. And once we reach 1000 tickets, we need to restart it from ‘1’.
But, we were maintaining multiple service data in multiple tables. So we need to maintain a global unique auto-number and here it comes to Sequence.

Lets proceed now step by step.

Step 1 :
First, you need to create a sequence object namely “Ticket”

CREATE SEQUENCE [dbo].[Ticket]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE
CACHE
GO

Step 2 :
You need to create two tables to maintain different types of services in different tables.

Create table tbl_Renewal_Application(
[TicketNo] INT NOT NULL,
[App Date] datetime,
[Comments] varchar(max)

Create table tbl_New_Application(
[TicketNo] INT NOT NULL,
[App Date] datetime,
[Comments] varchar(max))

Step 3:
Now, you need to insert the [Ticket No] (Auto Increment) in different tables without duplication and should be consistent.

INSERT tbl_Renewal_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket], Getdate(),'Renewal-1')
GO
INSERT tbl_New_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'New App-1')
GO
INSERT tbl_Renewal_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'Renewal-2')
GO
INSERT tbl_New_Application ([TicketNo], [App Date],[Comments])
VALUES
(NEXT VALUE FOR [Ticket] , Getdate(),'New App-2')

Now, if you browse the data from both tables, you can see the sequence number (Auto Number) available in the same sequence as we inserted into the table.
Here is the result :

Select * from tbl_Renewal_Application
Select * from tbl_New_Application

The beauty of this object is, if you need a new counter, you need not insert value in the table and then get the counter. By simply executing one query you can get the next counter.
Here is the query.

SELECT NEXT VALUE FOR dbo.[ticket]

–Result is 5
Note : At the time of implementation, if you need to start your first counter from 500 then, you need to set the start property of sequence to 500.