SQL Server 2011 introduces a fascinating feature called SEQUENCE, which has been highly anticipated by many developers. This feature allows you to define a single point of repository where SQL Server will maintain an in-memory counter. In this blog post, we will explore the concept of SEQUENCE and provide a working example to demonstrate its functionality.

To create a sequence, you can specify various values such as the start value, increment value, and maximum value. Let’s take a look at the following code:

USE AdventureWorks2008R2
GO

CREATE SEQUENCE [Seq] AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO

Once the sequence is defined, you can fetch its values using the NEXT VALUE FOR method. This method will provide a new incremental value each time it is called, regardless of the session. The sequence will generate values until the maximum value specified is reached. After that, the query will stop and return an error message.

-- First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO

-- Second Run
SELECT NEXT VALUE FOR Seq, c.AccountNumber
FROM Sales.Customer c
GO

If you want to restart the sequence from a specific value, you can use the RESTART WITH statement. Here’s an example:

-- Restart the Sequence
ALTER SEQUENCE [Seq]
RESTART WITH 1
GO

-- Sequence Restarted
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO

It’s important to note that the SEQUENCE feature offers a lot of useful functionalities, which we will explore in future blog posts. For now, let’s wrap up by cleaning up the sequence:

-- Clean Up
DROP SEQUENCE [Seq]
GO

That concludes our introduction to the SEQUENCE feature in SQL Server 2011. Stay tuned for more blog posts where we will dive deeper into the various aspects and benefits of this exciting feature.