SQL Server 2011 - SEQUENCE Part I

I have started playing with CTP1 of SQL Server 2011, (codename Denali). As you would expect, there are not a massive amount of new features included in this "first look", but over the Christmas period, I intend to post about some of the features that are available. The first of these is a long-awaited feature called a Sequence.

This is essentially a hugh extension to an identity, which only allowed you to specify a seed and an increment.

The first thing you notice about a sequence, is that it is not a column property, it is a separate, schema bound object. This means that you can run DDL statements against them, such as CREATE SEQUENCE, ALTER SEQUENCE, etc, and also means that you can run queries against them, to find out the next value, etc. Of course the biggest benefit, however, is that you can maintain a sequence across multiple tables.

The sequence object supports features, including Start with (equivalent of seed on an identity column), Min Value, Max Value, Increment (equivalent of Increment on an identity column)

I created a sequence object in the AdventureWorks2008 database, that mirrored a bog standard IDENTITY(1,1) column, to use as a starting point. To do this, I used the following statement...

CREATE SEQUENCE Sales.SalesOrderNumbers 
AS INT   
MINVALUE 1  
NO MAXVALUE   
START WITH 1

...I then used the following query, to pull back the first 3 numbers in the sequence...

SELECT NextOrderID=NEXT VALUE FOR Sales.SalesOrderNumbers
UNION ALL    
SELECT NEXT VALUE FOR Sales.SalesOrderNumbers
UNION ALL    
SELECT NEXT VALUE FOR Sales.SalesOrderNumbers

...And saw the following results...

..When I then ran the query again, it gave me the next 3 numbers...



...The next thing I tried, was resetting the sequence. To do this, I ran the following statement...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1

...Running the SELECT statement again, following this restart, produced the following output...

Next, I tried out the increment, by altering the sequence object with the following command...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 100
INCREMENT BY 10

...After this alteration, the SELECT statement produces the following results...

...I then changed the sequence object to...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 100
INCREMENT BY -10

...To produce the following results...

...Finally, I created a table with the following statement...

CREATE TABLE Sales.SalesOrders
(
EntID INT,
OrderNumber INT DEFAULT (NEXT VALUE FOR Sales.SalesOrderNumbers)
)

...and with the sequence altered as follows...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1
INCREMENT BY 10

...I ran the following INSERT statement...

INSERT INTO Sales.SalesOrders
 (
 EntID
 )
VALUES
 (1),
 (2),
 (3)

...A SELECT statement from Sales.SalesOrders table, then produced the following results...

...In summary, sequence seems easy to use, and adds some really useful functionality to SQL Server

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom