One thing applications tend to do when retrieving a lot of data from SQL Server is to page the results.

Since SQL Server 2005, I tended to do this using the ROW_NUMBER() function. Like this

SELECT *

FROM   (SELECT p.firstname,

               p.lastname,

               Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

        FROM   person.person p) AS person

WHERE  person.rownum BETWEEN 11 AND 20

or like this

WITH person(firstname, lastname, rownum)

     AS (SELECT p.firstname,

                p.lastname,

                Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

         FROM   person.person p)

SELECT *

FROM   person p

WHERE  p.rownum BETWEEN 11 AND 20

I would of course parameterize 11 and 20 and call that in a stored procedure or parameterized SQL statement. Linq to Sql or the Entity Framework currently generates a similar SQL statement.

OFFSET ... FETCH NEXT 

Now in SQL Server 2012 RC0 we can achieve the same thing using the offset  ... fetch next clause.

The Order By statement now has an additional clause we can use as follows:

SELECT p.firstname,

       p.lastname

FROM   [Person].[Person] p

ORDER  BY p.firstname DESC


OFFSET 10 rows

FETCH NEXT 10 rows only

The offset clause specifies the number to rows to skip before returning the rows from the query expression.

The fetch specifies the number of rows to return after the OFFSET clause has been processed.

In terms of performance, I have not done any extensive performance tests but I do not believe there is a significant performance difference between the two.

The query execution plan for the two statements are similar.

James

At long last SQL Server gets Sequences. The community had been asking for this ANSI feature for some time now. ORACLE has had this since its inception.

A sequence is simply a user-defined object that is used to generate a number sequence according to a set of rules that are specified when the sequence in created.

The rules that govern sequence creation are as follows:

• Create sequences in ascending or descending order
• Control the starting point and intervals
• Specify the maximum and minimum values
• Be configured to restart (or cycle) when exhausted
• Cache sequence values to improve performance.

Applications can obtain the next sequence number by calling the NEXT VALUE FOR function.

The syntax for creating sequences is as follows:

 CREATE SEQUENCE [schema_name . ] sequence_name  
   [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
   [ START WITH <constant> ]  
   [ INCREMENT BY <constant> ]  
   [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
   [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
   [ CYCLE | { NO CYCLE } ]  
   [ { CACHE [ <constant> ] } | { NO CACHE } ]  
   [ ; ]  

Consider the following sequences:

 Create Schema SeqTest;  
 CREATE SEQUENCE SeqTest.Counter1  
   START WITH 1  
   INCREMENT BY 1  
 ;  
 Create SEQUENCE SeqTest.Counter100  
      Start with 100  
      INCREMENT BY 50  
      MINVALUE 100  
      MAXVALUE 300  
      CYCLE  
      CACHE 3  
 ;  

SeqTest.Counter1 creates a sequence that starts with 1 and increments by 1 whenever the NEXT VALUE FOR function is called.
The second sequence starts with 100 and increments by 50. Once 300 has been reached, the sequence restarts from 100.

Let us try this out:

 CREATE TABLE SeqTest.Person  
   (  
           ID int PRIMARY KEY,  
           Name varchar(20) NOT NULL,  
           Ranking int  
   );  
 GO  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'John', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jane', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Bill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Ann', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jack', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Frank', Next Value for SeqTest.Counter100) ;

select * from SeqTest.Person 

ID	Name	Ranking
1	John	100
2	Jane	150
3	Bill	200
4	Ann	250
5	Jack	300
6	Jill	100
7	Frank	150

Data Types
Sequences can be used with the following data types :
tinyint, smallint, int, bigint, decimal and numeric( scale of 0). If the data type is not specified, it defaults to bigint.

Resetting Sequences

Sequence can be reset as follows:

 ALTER SEQUENCE SeqTest.Counter1  
 RESTART WITH 1 ;  

Cache and No Cache Option
The cache option is used to increase performance by caching the current value and number of values left in the cache. This minimizes the number of disk IOs required to generate sequences.

Transaction consistency
Sequences are not transactionally consistent and run outside the scope of the current transaction. So it is possible for gaps to appear in the sequence. If a transaction is rolled back, the next value is still taken.

 CREATE SEQUENCE SeqTest.Counter2 AS smallint    
      START WITH 1    
      MAXVALUE 100   
      ;  
 BEGIN TRANSACTION;    
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2; --displays 1 
 ROLLBACK TRANSACTION;  
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2;  --displays 2

Stay tuned. In my next post, I will look at when to choose Sequences over Identity columns.
James