Ralph Varjabedian
Coding is a systematic art

Paging made easy in MS SQL Server 2005

Wednesday, April 09, 2008 5:32 PM

SQL server 2005 has a new function added to it, Row_Number(). It allows you to easily implement paging when you are getting your data.

Suppose you have a simple query like :

select * from users

To prepare it for paging support, it will become:

select Row_Number() over (order by userID) as RowIndex, * from users

The new column returned called RowIndex will have the Index of the rows (zero based), the first row being 0, the second row being 1 and so on. Usually you return the order based on your primary key (in this case userID) but you can put any order you want.

Let us say we have a page size of 10 and we want page 3 then this means we would want records with indexes between 30 and 40 then this statement becomes:

select * from 
    (select Row_Number() over (order by userID) as RowIndex, * from users) as Sub
    Where Sub.RowIndex >= 30 and Sub.RowIndex < 40

The math behind paging is easy, StartIndex = PageSize * PageNumber

and EndIndex = StartIndex + PageSize. Note how the filter is inclusive on the StartIndex and exclusive on the EndIndex.

You can also use the top statement as well.


Feedback

# re: Paging made easy in MS SQL Server 2005

Very interesting. I was wondering if you'd had any chance to run this against a table of several million records, ie does it scale well or is it, in effect, creating a temp table of the entire "users" table with just one additional column?

I used to do something similar with SQL 2000, but had to jump through a ton of additional hoops to keep it from killing the machine when paging through substantial result sets. 5/2/2008 4:12 PM | Darin

# re: Paging made easy in MS SQL Server 2005

Hi, Thanks for sharing. Well, Row_Number is recommended by microsoft as the way to do paging, however I have read several places on the Internet before that they are having problem with this method as being not fast enough when the sets are very large, in the order of million of records. However maybe with some clever usage if Indexes, this should not be a problem. 5/2/2008 4:37 PM | Ralph Varjabedian

# re: Paging made easy in MS SQL Server 2005

No. It won't work with big table with millions of records. It's way too slow. 5/6/2008 11:30 PM | Jiang

# re: Paging made easy in MS SQL Server 2005

Hi Jiang, do you know of an alternative for this when the table is that big? Thanks. 5/7/2008 12:19 AM | Ralph Varjabedian

# re: Paging made easy in MS SQL Server 2005

Keep id(to unique identify)and an updated modify date-time in another table and use between in SQL via id coloumn. Find the max id while paging and just handle id numbers with math by mod.
ı wonder if this will work because i am looking for a suitable paging method for my large tables... 6/3/2008 2:21 PM | Çağlarro

# re: Paging made easy in MS SQL Server 2005

What does an user want when he is searching (and paging) for data? He probably expects a few pages based on the sortation and selection criteria he just gave in. Not million of records. He rather alters these criteria than he will be paging for thousands of pages. At least I will/would. Offer him a prefilled - for him to alter - textbox with the maximum number of records he will get. You may use a top statement for this. 9/11/2008 2:36 PM | Eric Gehring

# re: Paging made easy in MS SQL Server 2005

True, however sometimes there is no escape from listing all your data :) 9/12/2008 11:59 AM | Ralph Varjabedian

# re: Paging made easy in MS SQL Server 2005

What if few records are deleted in users table.

UserID = 1, 5, 6, 7, 10

Will the below condition work perfectly..?

Where Sub.RowIndex >= 30 and Sub.RowIndex < 40 3/4/2010 3:03 PM | Gautham

# re: Paging made easy in MS SQL Server 2005

if you have million of records, this will become very slow when viewing the last page. Alternative way of doing this is:

assuming there are more than 2100000 records, this is page 2100 has 1000 record.

select top 1000 * from Table1 where id not in (select top 2100000 id from Table1) 3/25/2010 10:52 PM | Andy

Post a comment





 

Please add 6 and 3 and type the answer here: