CODEWITHSUNDEEP
sql-serversql-server-2012
mehdi lotfi
mehdi lotfi

Reputation: 11571

Usage of sequence in SQL Server 2012

I have SQL Server 2012 and I want to know what's the usage of sequence. I Look for a sample to explain usage of sequence.

EDIT

I know create and use Sequence in database. I want to know what is practical scenario for use of Sequence.

Upvotes: 7

Views: 3682

Answers (2)

Keith
Keith

Reputation: 21244

CREATE SEQUENCE dbo.OrderIDs
    AS INT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
2
3

See here for original source and more examples. The page refers to SQL Server Denali which is the beta of SQL 2012 but the syntax is still the same.

Upvotes: 5

Techie Joe
Techie Joe

Reputation: 867

One of the ways I leverage the SEQUENCE command is for reference numbers in an ASP/C# detailsview page (as an example). I use the detailsview to enter requests into a database and the SEQUENCE command serves as the request/ticket number for each request. I set the inital sequence command to start with a specific number and increment by 1 for each request.

If I present these requests in a gridview I make the SEQUENCE reference numbers appear but don't make them editable. Its great for a reference number when records are similar with other fields in the database. It's also perfect for customers when they have questions about a specific entry in a given database. This way I have a unique number per entry no matter if the rest of the information is identical or not.

Here's how I generally leverage the SEQUENCE command:

CREATE SEQUENCE blah.deblah
     START WITH 1
     INCREMENT BY 1
     NO CYCLE
     NO CACHE

In short, I start my sequence at #1 (you can choose any number you want to start with) and it counts upwards in increments of 1. I don't cycle the sequence numbers when they reach the system max number.

Upvotes: 2

Related Questions