ORDER BY in Denali

The ability to efficiently “page” results in SQL Server using t-sql has been a topic of blogs and forums for quite some time.  The CTP of SQL Server Denali introduces just this feature and brings an end to the “work arounds” or client side processing.  One of the t-sql enhancements introduced to Denali are the clauses OFFSET and FETCH, used in combination with ORDER BY, which provides the ability to “page” through a result set. 

Paging is quite often seen within applications allowing users to view a page of 5, 10, 20, etc. rows or results and to then click or navigate to view the next page of the specified number of results on a new page.  This type of feature is most often provided through the user interface rather than through t-sql.  Typically the t-sql code will return a full result set and allow the application to cache this client side, but can result in some unnecessarily large results being returned upon click or post.

To demonstrate this I will use the examples provided directly from books on line for SQL Server Denali.  The below query returns all 16 rows from the  Department table in the Human Resources schema AdventureWorks2008R2 database and is ordered by the DepartmentID:USE AdventureWorks2008R2;
GO
-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

A common request would be to return 5 rows at a time until all results have been returned.  This can be accomplished with the following queries:


-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 0 ROWS
    FETCH NEXT 5 ROWS ONLY;

-- Skip 5 rows and return only the first 5 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 5 ROWS
    FETCH NEXT 5 ROWS ONLY;


-- Skip 10 rows and return only the first 5 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 10 ROWS
    FETCH NEXT 5 ROWS ONLY;

-- Skip 15 rows and return only the first 5 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 15 ROWS
    FETCH NEXT 5 ROWS ONLY;


The above 4 queries return all 16 rows in four different result sets:

You can tell from the syntax that OFFSET is used to specify the row in the result set in which to begin while FETCH NEXT specifies the number of rows to be included.  BOL includes an example of returning all rows in in a single transaction:

USE AdventureWorks2008R2;
GO

-- Ensure the database can support the snapshot isolation level set for the query.
IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2008R2') = 0
    ALTER DATABASE AdventureWorks2008R2 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Set the transaction isolation level  to SNAPSHOT for this query.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

-- Beging the transaction
BEGIN TRANSACTION;
GO
-- Declare and set the variables for the OFFSET and FETCH values.
DECLARE @StartingRowNumber INT = 1
      , @RowCountPerPage INT = 3;

-- Create the condition to stop the transaction after all rows have been returned.
WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber
BEGIN

-- Run the query until the stop condition is met.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
    OFFSET @StartingRowNumber - 1 ROWS
    FETCH NEXT @RowCountPerPage ROWS ONLY;

-- Increment @StartingRowNumber value.
SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;
CONTINUE
END;
GO
COMMIT TRANSACTION;
GO

I see a great deal of potential in application development to utilize this feature in place of posting the “SELECT * FROM” queries that rely on client side processing to page the results.  The trick will be assuring consideration is given to the performance of one query opposed to many.

The online documentation can be found here:

http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx?appId=Dev10IDEF1&l=EN-US&k=k(SQL11.PORTAL.F1)&rd=true