Query paging by one trip to MS-SQL Server 2005 via one simple query

• Download demo project - 19.8 KB

Introduction

If you don't use DataGrid and all the other fabricated user controls in your website, then you must resolve how to create a paged query by minimum number of interactions with the database server. Popular web database servers like MySQL or Firebird use proprietary syntax like "LIMIT 20, 10" or "first 5 skip 2" etc.

You must recognize how many records are in your result and then calculate the number of pages and all items which are required in order to make a paged query. And this means two trips to the database, which is not very convenient when a table is large, extended, enormous, and slow. In MS-SQL Server 2005 Common Table Expressions (CTE), it is possible to do a paged query in one trip to the database server, and you needn't use a Stored Procedure, View, or other features.

This article explains how to make this work only with T-SQL and C#.

For example: you have a simple SQL query on the AdventureWorks database like:

SELECT ProductID , [Name], ProductNumber, MakeFlag, FinishedGoodsFlag, 
   Color, SafetyStockLevel
FROM Production.Product 
WHERE ProductNumber like 'BK%' ORDER BY [Name] DESC

It's necessary to extract the "Order By" clause and crop the first word ("SELECT"). The order field is required!!! The function Row_Number doesn't work without the order field. If you don't have any usable field, it is recommended to use a Primary Key in the Order By clause. This query is a little longer, but you can replace my declared T-SQL variables in your application code and use discreet values, hen you build this query. The original query is visible.

declare @explicitMaximum int
declare @chosenPage int
declare @recordsPerPage int
set @explicitMaximum = 100
set @chosenPage = 2
set @recordsPerPage=10;

WITH X AS (
SELECT TOP(@explicitMaximum)
ROW_NUMBER() OVER (ORDER BY [Name] DESC) AS RowNumber, -- extracted order 
                                                       -- clause
CASE WHEN COUNT(*) OVER(PARTITION BY '') >@explicitMaximum THEN 
    @explicitMaximum ELSE COUNT(*) OVER(PARTITION BY '') END as totalCount,
------------ Original query
ProductID , [Name], ProductNumber, MakeFlag, FinishedGoodsFlag, Color, 
    SafetyStockLevel
FROM Production.Product 
WHERE ProductNumber like 'BK%' ORDER BY [Name] DESC
------------
) SELECT 
(totalCount-1)/@recordsPerPage+1 as actualPageCount, *
FROM x WHERE (RowNumber-1)/@recordsPerPage+1 = CASE WHEN (
   totalCount-1)/@recordsPerPage+1<@chosenPage THEN (
   totalCount-1)/@recordsPerPage+1 ELSE @chosenPage END

You must receive three new redundant fields {actualPageCount, RowNumber, totalCount}, but only RowNumber is really useless. All others are necessary when you want to use this result on your web. If the paged result has only 20-50 records, it's not very crucial and you can use any bizarre name for these fields. This composition is resistant to events like: @explicitMaximum is less then count(*) in your query, or @chosenPage is higher than actualPageCount, but this event must be handled in the web application as well, and the recently chosen page must by affected.

The C# code which extracts the order field from your query is in the attached project to this article. The Order clause can only be once in the query and only in the external Select (not within apostrophes). It's possible to split SQL queries by space and then use this array to find the Order clause.