CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2pagination
Cassie
Cassie

Reputation: 474

Efficient pagination in SQL Server 2008 R2

I want to write a stored procedure that does the following: * Returns the number of records matching a query (to determine the number of pages to show the user) * Returns another recordset with a certain subset of records from a query for the current page.

From another question I found on StackOverflow (sorry, I lost the link), I found that we do pagination like this:

SELECT u.* FROM 
(
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) as rownum FROM
    (
        [obscenely long select query]
    )
    as t
)
AS u WHERE u.rowNum >= 1 AND u.rowNum <= 20

And I can get the number of records returned by [obscenely long select query] by doing the following:

SELECT COUNT(*) AS numRecords from ([obscenely long select query]) AS records

I want to get both of these recordsets as effectively as possible.

The only way I can figure how to do it is shoving both of those statements above into one stored procedure. This strikes me as inefficient for a few reasons:

Other solutions I've tried:

Is there anything that can be done? Should I just suck it up and stick with my current solution, or is there a more elegant way to pull this off?

Upvotes: 2

Views: 1647

Answers (2)

Nithin Gangadharan
Nithin Gangadharan

Reputation: 527

One soluntion for implementing pagination is:

Your stored procedure will following parameters

@PageSize INT,
@PageNumbers INT

;WITH CTE AS(
        SELECT a,
               b,
                ROW_NUMBER() OVER(ORDER BY a) RN
        FROM    TableName
)
SELECT  *
FROM    CTE
WHERE   RN  BETWEEN ((@PageNumbers - 1) * @PageSize + 1) AND (@PageNumbers * @PageSize)

If you pass @PageNumbers = 1 and @PageSize = 10 Then 

((@PageNumbers - 1) * @PageSize + 1) = ((1 - 1) * 10 + 1) = 1
and (@PageNumbers * @PageSize) = (1 * 10) = 10

Similarly, if @PageNumbers = 2 Then 

((@PageNumbers - 1) * @PageSize + 1) = ((2 - 1) * 10 + 1) = 11
and (@PageNumbers * @PageSize) = (2 * 10) = 20

You can even hard code @PageSize value in your SP.

Upvotes: 0

Paolo
Paolo

Reputation: 2254

depending on the [obscenely long select query] structure you may be able to use a temp table or table variable:
- fill a temp table with the ids of the matching rows
- count the temp table rows to calculate the number of pages
- to retrieve the results for the caller build a query with the temp table joined with the relevant database tables

Upvotes: 1

Related Questions