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