Ad-hoc Pagination Support with SQL Server codenamed “Denali”

Microsoft has announced the first Community Technology Preview (CTP) of its next generation SQL Server product, codenamed “Denali” yesterday. SQL Server “Denali” has many new features and enhancements, including these for the programmability and manageability features which you may be interested in:

• Visual Studio 2010 based Management Studio tools
• Advanced T-SQL debugging support
• Code snippets and IntelliSense enhancements
• Contained database
• EXECUTE statement enhancements with RESULT SETS
• OFFSET and FETCH sub-clause of ORDER BY clause
• SEQUENCE objects
• Enhancements to the geometry data types

To download SQL Server “Denali” CTP 1, please go to this page in Microsoft Download Center. http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9.

In this article, I will discuss the ad-hoc pagination support with SELECT, ORDER BY and OFFSET query.

The Problem

In a data-centric Web or Windows application, one of the developer’s common task is to seek for a result set from the database which meets the specified query conditions, then display the result set in the User Interface. Suppose the application targets the Windows Phone 7 platform, and the result set is large, the user interface may not have enough space to render all the data in this result set in a single frame, therefore, the developer has to add a vertical scrollbar to the user interface so that the end user can scroll up or down to view the remaining data rows; in addition, if the result set is huge, the scrollbar may be very short, it takes the user a long time to scroll down from the top to bottom, the user may also have difficulties to find what he wants in this long list. In this case, the result set must be split into pages that makes the vertical scrollbar longer, so the application can be easy to navigate to the data that the user intended. By using paging, the application can be easier to use and more responsible. This is called pagination.

Let’s say we are going to do pagination with a SQL Server database, before SQL Server “Denali”, there is no direct support for the pagination, alternatively, the following methods are commonly used in the community.

1. Use TOP and EXCEPT
2. Use ROW_NUMBER and OVER with Common Table Expression (CTE)

Use TOP and EXCEPT

The following T-SQL code shows how to use TOP and EXCEPT with a SELECT query to implement pagination.

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
SELECT TOP (@pageSize * @pageNumber) *
FROM dbo.[Event]
EXCEPT
SELECT TOP (@pageSize * (@pageNumber - 1)) *
FROM dbo.[Event];
GO

The algorithm is like this: the first part of SELECT TOP statement seeks for the data with size equals to @pageSize times @pageNumber. This selects all previous and current page data, then, use the EXCEPT projection to eliminate previous page data by using another SELECT TOP statement, with size equal to @pageSize times @pageNumber subtracts 1. The resulting set is the data start from (@pageSize * (@pageNumber – 1) + 1) and end with (@pageSize * @pageNumber).

When I run this code in SQL Server Management Studio, I get the following output:

Use ROW_NUMBER() and OVER with Common Table Expression

Actually, if the seeking table has a identity number for each row, the paging problem is no longer a big problem, because you can calculate the start and the end row number for a specified page number, then use a WHERE clause to filter the query.

To achieve this, SQL Server provides a ROW_NUMBER() function, which can be used to generate a dynamic row identity number (start from 1 and increment by 1); with a Common Table Expression, you can easily add this row number column temporarily to the seeking table result set, then, use WHERE clause to implement pagination.

The following T-SQL shows how to use ROW_NUMBER() to do pagination.

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
WITH TempEvent AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
	FROM dbo.[Event]
)
SELECT * FROM TempEvent
WHERE RowNumber BETWEEN (@pageSize * (@pageNumber - 1) + 1)
AND (@pageSize * @pageNumber)

When I run this code, I get the exact same result as the first example.

Use OFFSET and FETCH with ORDER BY Clause

SQL Server “Denali” enhances ORDER BY clause by adding OFFSET and FETCH sub-clause to specify how many rows to skip to return and how many rows should be returned. The enhanced syntax for ORDER BY is:

ORDER BY order_by_expression
     [ COLLATE collation_name ]
     [ ASC | DESC ]
     [ ,...n ] 
[ <offset_fetch> ]

<offset_fetch> ::=
{    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
     [
       FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression }
        { ROW | ROWS } ONLY
     ]
}

The following T-SQL code shows the usage of the OFFSET and FETCH with ORDER BY to implement pagination.

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
SELECT *
FROM dbo.[Event]
ORDER BY CreatedTime DESC
OFFSET @pageSize * (@pageNumber - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY;

When I run this code, I get the following output:

Now you have an extremely simple way to have paginations in your application!

Note: The ADO.NET Entity Framework, LINQ to SQL and LINQ to Object supports pagination by using Skip() and Take() extension methods. For the current Entity Framework and LINQ to SQL implementation, it is possible to generate T-SQL that use ROW_NUMBER() function, it won’t generate new OFFSET and FETCH T-SQL code until the Entity Framework and the LINQ to SQL providers take the change in a future version of .NET Framework. It is obvious to predict that in the coming .NET Framework 5.0 and the Entity Framework 5.0, the integrity of SQL Server “Denali” with them will play an important role as part of the ADO.NET data access techniques.