Advertisements
Posted by: jsonmez | July 22, 2010

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 “paging sql server”, I’ll give you my best answer for it at this point.

(Google search term additions above for helping people find help)

paging

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:

SELECT name, goodnessfactor, color
FROM vegetables
WHERE color = 'purple' OR color = 'green'

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)

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

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.

As always, you can subscribe to this RSS feed to follow my posts on Making the Complex Simple.  Feel free to check out ElegantCode.com where I post about the topic of writing elegant code about once a week.  Also, you can follow me on twitter here.
Advertisements

Responses

  1. 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.

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

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

    • Very nice, thanks!

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

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

  4. 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: