paging.jpg

News Flash: SQL Server Paging Still Sucks!

Last time I wrote some code to allow paging of stored procedure results in SQL Server it sucked.

That was about 3-4 years ago.

I just wrote some code to do it again, and well…  It still sucks.

Perhaps I’m doing it wrong, but if you are looking for, “how to implement paging in sql server”, or “how to page sql server stored procedure results”, or “sql server paging”, I’ll give you my best answer for it at this point.

paging thumb News Flash: SQL Server Paging Still Sucks!

How I roll with CTEs

CTEs or Common Table Expressions, are a pretty nice feature of SQL Server.  They are a bit complex, but basically they let you create a query and treat it as a table to select from.  They also allow recursive calls to themselves, which let you do cool things like query hierarchies of data.

This link offers a pretty good explanation of CTEs.

Anyway, CTEs come in handy combined with the ROW_NUMBER() function to add the ability to page data from an existing stored procedure.

The basic idea here is to modify the original query by wrapping it inside of a CTE that adds a RowNumber column.

Then we can select from that query the rows that are in the range we want.

Vegetables are good for you

Let’s look at an example:

[sourcecode language=”sql” padlinenumbers=”true”] SELECT name, goodnessfactor, color
FROM vegetables
WHERE color = ‘purple’ OR color = ‘green’
[/sourcecode]

Now let’s modify this query to be pageable given we have the first and last record we want.  (first record = page number * rows per page, last record = page number + 1 * rows per page)

[sourcecode language=”sql”] WITH pagedVegetables AS (
SELECT name, goodnessfactor, color,
ROW_NUMBER() OVER (ORDER BY name) AS rownumber
FROM vegetables
WHERE color = ‘purple’ OR color=’green’
)
SELECT *
FROM pagedVegetables
WHERE rownumber > @firstRecord AND
rownumber <= @lastRecord
[/sourcecode]

Okay, so it is actually not that bad.

A couple of notes to help understand what happened here.

  • ROW_NUMBER() requires an OVER clause which basically will determine how you calculate what the row number is.  In this case, we are going to sort by name.  So, the first alphabetical name will be row number 1, and so on.
  • We’ve wrapped the query in a CTE and added an additional column so that we have a row number in our new virtual table.  (Think of the CTE as creating a temporary view for us.)
  • We are selecting everything out of the original query that is in the range of rows we want.  (Depending on how you define your range you may use different equality operators.  If you range is inclusive, you would do <= and >= , and if it is exclusive it would be < and >.  In this case we are both.)

A word on total row count

If you need the total number of rows so that you can pass that back to the pager in your code, then you will probably have to select the results from the CTE into a temporary table and return two result sets from it.

I couldn’t really find a way to select the total rows and the data from a single CTE expression.  If you know a way, please let me know.

  • http://www.dougv.com/blog Doug Vanderweide

    As an added note, John, one could replicate the functionality of your CTE results set with a view:

    CREATE VIEW pagedVegetables AS (
    SELECT name, goodnessfactor, color,
    ROW_NUMBER() OVER (ORDER BY name) AS id
    FROM vegetables
    WHERE color = ‘purple’ OR color=’green’
    )

    The benefit over CTE being that one would now have the next-best thing to a permanent table, against which to work with this results set.

    So, for example, if you needed to be able to execute several different stored procedures / queries against the same results set, one might choose a view vs. CTE; but for a single paging query, what you have done here is excellent.

  • http://test.roob.dk Carsten Petersen

    Under you’re current rownumber, you can add the following line, to extract the rowcount:

    COUNT([name]) OVER (PARTITION BY ”) As recordcount

    • http://simpleprogrammer.com jsonmez

      Very nice, thanks!

  • Patrik

    I have the same problem with the total count. Did you find a solution? What is this code posted by Carsten doing?

    • http://simpleprogrammer.com jsonmez

      It’s been a while since I posted this, but I believe Carsten’s solution had worked for making it all in one query.

  • http://arunraj.co.in Arun Raj

    Try this link: http://www.arunraj.co.in/index.php?option=com_content&view=article&id=2:paging-query&catid=3:aspnet&Itemid=8
    You’ll find 4 types of paging in SQL Server and a C# function which will allow you to convert any query to a paging query. You just have to pass the start and end values.