Pi

October 23, 2009

Sql Server Pagination With Total Number of Records

Filed under: SQL — Tags: , — rqmedes @ 1:00 am

Pagination is  easy in most scenarios

You usually handle it in code letting your favourite  ORM such as NHibernate  take  care of it for you.
There are times when you are required to hand-roll your own sql which is as simple as

SELECT * FROM <Table>  WHERE <Criteria> LIMIT 0, 10

or for SQL Server

ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber

But this doesn’t  return the total number of  records,  important for  presenting   ‘Page 1 of 380‘.
I assume  most would  do an additional sql call.

SELECT  COUNT(*)  FROM  <Table>  WHERE <Criteria>

In SQL Server it is easy to get both in one query.

WITH Results AS (
SELECT ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber,
ROW_NUMBER() OVER (ORDER BY <Column> DESCAS RowNumberDesc,
* FROM <Table>
WHERE <Criteria>)
SELECT * FROM Results  WHERE RowNumber BETWEEN <Start> AND <End>

This will return the results  between <Start> and  <End>  ordered by <Column>.
You can  get the Total Number of records  by adding the <Start> value to the  RowNumberDesc  value of the first record returned.
Preliminary benchmarking shows that it performs faster than running the two queries method.

Advertisements

2 Comments »

  1. As a note I found that Ryan Stille at
    http://www.stillnetstudios.com/pagination-mssql-2005/ has an even nicer solution
    Regards,
    Darryl

    Comment by rqmedes — October 23, 2009 @ 1:28 am

  2. […] Pagination in MSSQL with total number of records. […]

    Pingback by Postgres Paginations « Pi — November 23, 2009 @ 3:48 am


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: