Microsoft released the first CTP version of SQL Server Denali during the SQLPASS Community Summit in Seattle last year. Since this major event in the SQL Server community I had a little bit of time to look into the CTP 1 version of Denali and see what new features they are providing us. 2 interesting features in the area of T-SQL I want to cover today throughout this blog post: Sequences and Paging

Let’s start with sequences first. If you already worked with Oracle you should be already familiar with sequences, because they are used everywhere in the database design. For SQL Server sequences are a completely know concept. A sequence is nothing else like an INT IDENTITY column, but with the difference that a sequence is created on the database level and can be used and accessed through all your database objects (like tables, stored procedures, stored functions, etc.). Sequences are created with the new T-SQL statement CREATE SEQUENCE:

CREATE SEQUENCE MyNewSequence
    AS INT
    MINVALUE 1
    MAXVALUE 1000
    START WITH 1
GO

As you can see, the sequence get’s a name which must be unique within the current database. Then you have to specify the underlying data type of the sequence, like TINYINT, SMALLINT, INT, BIGINT. You can also specify a minimum and maximum value (MINVALUE, MAXVALUE) and which value your sequence should return, when the sequence is accessed for the first time (START WITH). To track all your sequences within a database, SQL Server provides you a new system view – sys.sequences:

SELECT * FROM sys.sequences
GO

After you have created a new sequence you can start using it through the new command NEXT VALUE FOR:

DECLARE @id INT
SET @id = NEXT VALUE FOR MyNewSequence
SELECT @id
GO

In this case SQL Server retrieves the next value from the specified sequence. In this case you get back the value 1. If you execute the statement several times, SQL Server returns sequentially the value 2, 3, 4, 5, 6, etc. It’s the same like a INT IDENTITY column, but defined and used on the database level. Existing sequences can be also altered through the ALTER SEQUENCE statement:

ALTER SEQUENCE MyNewSequence
    RESTART WITH 10
    INCREMENT BY 10
    NO MAXVALUE
GO

As you can see, you can restart the sequence on a specified value, and you have also the possibility to specify an increment value and that you don’t want to use a maximum value. If you specify the NO MAXVALUE option, the maximum value is defined through the underlying data type, that is used by that sequence. Therefore it is also not allowed to define the following sequence:

CREATE SEQUENCE InvalidSequence
    AS TINYINT
    MINVALUE 260
    MAXVALUE 300
    START WITH 260
GO

The data type TINYINT has a maximum value of 255 in SQL Server, therefore you can define with this underlying data type a sequence for the range of 260 to 300. SQL Server will return you an error message. You are also not allowed to define sequences that are counting down, like:

CREATE SEQUENCE InvalidSequence
    AS TINYINT
    MINVALUE 200
    MAXVALUE 1
    START WITH 200
    INCREMENT BY -1
GO

Another nice feature of sequences is cycling, which means that the sequence returns the first values in the sequence when it has reached the specified maximum value. See the following example for an explanation:

CREATE SEQUENCE CyclingSequence
    AS INT
    MINVALUE 1
    MAXVALUE 10
    CYCLE
GO

DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence
SELECT @id
GO 15

The first statement creates a cycling sequence with a range from 1 to 10. If you execute the next batch 15 times (GO 15), SQL Server returns 15 values from the sequence: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5 – the sequence is just cycling through its range.

If you are working with sequences inside transactions you must be also aware that sequences are not providing you a consistent gap-free stream of values. This is important when your transaction is rolled back:

BEGIN TRANSACTION
DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence
SELECT @id
GO

ROLLBACK TRANSACTION
DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence
SELECT @id
GO

If you have retrieved a value from a sequence and you finally rollback your transaction, then the value is consumed, and NEXT VALUE FOR returns the next value from your sequence. Short story: sequences are not aware of transactions! If you want to retrieve a whole range of sequence values, you don’t have to use a FOR loop for it. SQL Server provides you in this case the handy system stored procedure sys.sp_sequence_get_range:

DECLARE @firstValue SQL_VARIANT, @lastValue SQL_VARIANT;
EXEC sys.sp_sequence_get_range
    @sequence_name     = ‘CyclingSequence’,
    @range_size        = 3,
    @range_first_value = @firstValue OUTPUT,
    @range_last_value  = @lastValue OUTPUT;
SELECT 
    FirstValue = CONVERT(INT, @firstValue), 
    LastVlaue = CONVERT(INT, @lastValue),
    NextValue = NEXT VALUE FOR CyclingSequence
GO

The important point to mention here is the fact, that sys.sp_sequence_get_range just marks a range with the starting- and ending value as used. That means that you can use the sequence values within this range without ever retrieving them explicitly. If you mark for example the range from 5 to 10 as used, then you can use the values 5, 6, 7, 8, 9, and 10 completely at your own inside your logic. You don’t have to retrieve them again through NEXT VALUE FOR, because the whole range was already allocated through sys.sp_sequence_get_range. When you don’t need your sequence anymore you can delete it from your database:

DROP SEQUENCE MyNewSequence
GO

As you can see from this explanation sequences are a very handy concept when you want to work with unique values across your whole database. In the past I have seen a lot of database designs where unique values where needed across different tables. That was not possible with INT IDENTITY values, because they are scoped to a specified table. Some people used here a centralized table which stored the current value that was used across the database. That approach is not needed any more with sequences. From an application development perspective sequences are also very cool, because you can retrieve a unique value from SQL Server before ever committing your record to the table (like with INT IDENTITY values, which are calculated only when a new record is inserted into a table). Therefore you have now really no reason anymore to use GUIDs as PKs (and therefore by default as clustered keys) in your database design, which causes you A LOT of overhead and index fragmentation throughout your whole database. Kimberly Tripp has written in the last month a few post blogs about this specified problem, and I recommend to to read it, and think very careful about it when you have defined PKs on UNIQUEIDENTIFIER data types…

Another new nice feature in SQL Server Denali is NATIVE paging support, which means you don’t have to use a workaround which is possible with the ROW_NUMBER() function introduced with SQL Server 2005:

DECLARE
	@PageSize    TINYINT = 10,
	@CurrentPage INT     = 100;
WITH o AS
(
    SELECT TOP (@CurrentPage * @PageSize)
        [RowNumber] = ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID),
        SalesOrderDetailID,
        OrderQty,
        UnitPrice,
        LineTotal
    FROM
        Sales.SalesOrderDetail
)
SELECT
    SalesOrderDetailID,
    OrderQty,
    UnitPrice,
    LineTotal
FROM o
WHERE
    [RowNumber] BETWEEN ((@CurrentPage – 1) * @PageSize + 1)
    AND (((@CurrentPage – 1) * @PageSize) + @PageSize)
ORDER BY
    [RowNumber]
GO

When you want to use paging, you specify the record position that you want to retrieve first from your result set. If your result set holds 1000 records, and you specify the OFFSET of 500, SQL Server just skips the first 499 records, and starts returning back your records at the 500th record. Finally you can specify with FETCH NEXT the amount of records that you want to get back starting at the specified OFFSET:

DECLARE
    @PageSize    BIGINT = 10,
    @CurrentPage INT     = 100;
SELECT
    SalesOrderDetailID,
    OrderQty,
    UnitPrice,
    LineTotal
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID -- The ORDER BY clause is needed by the paging mechanism!!!
OFFSET (@PageSize * (@CurrentPage – 1)) ROWS -- Specifies the number of rows to skip before it starts to return rows from the query expression
FETCH NEXT @PageSize ROWS ONLY -- Specifies the number of rows to return after the OFFSET clause has been processed
GO

Please be aware that the paging functionality always needs a sorted result set, therefore you have to sort it through ORDER BY, otherwise SQL Server returns you an error message.

-Klaus