Click here to Skip to main content
13,256,012 members (49,410 online)
Click here to Skip to main content
Add your own
alternative version


133 bookmarked
Posted 26 Mar 2007

SQL Server 2005 Paging Results

, 19 Apr 2007
Rate this:
Please Sign up or sign in to vote.
Using SQL Server to do paging of sql results.


With SQL Server 2005, it is now a lot easier to use paged queries than in previous versions. I will be using the NorthWind database (mostly), so you can also use the examples I have provided. I will keep the examples simple; anything complex will only cause confusion. I will start with "traditional" methods such as SELECT, TOP, and then move on to the specific SQL Server 2005 paging examples.


I was asked a question or a series of questions, "How would you do paging in a SQL? How would you do this with a lot of records, say, 10,000 or more?"

I thought about answers. To be more precise, I thought of more questions and this got me thinking, "This must be a common problem, every developer must have done or solved this. What about paging sizes and working with very large data sets? What about getting results from multiple tables?"

So, I decided to look into these questions with specific reference to SQL Server 2005. The following is by far the easiest way and should be used, but it is rarely this easy.

select * from mytable
Where ID between 20 and 30


SQL Top (returns records from the TOP of the result set) is very good at returning a set number of records from each end of a results set. The example below gets the top 10 customers by order qty. This is a very common question on forums. TOP can also pull a percentage of records, although this isn't discussed here.

select top 10 * from customers -- This is a very basic example.
select TOP 10 Customers.CustomerID, Customers.CompanyName, count(*) OrderCount
from Customers inner join Orders on Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName

This is really useful. When you want to pull records 11 to 20 you could use temp tables.

-- SELECT First 30 records in to Temp table
from Customers
ORDER BY CompanyName ASC

--Select Bottom 10 records in another temp table

from #Temp



This is fine for the first few pages or the first few users. If you have users that want to return page after page after page, you end up getting 1000 records to return 10, which is not very efficient. You could also have placed an identity on the first temp table and used a SELECT statement as an alternative.

Alternative to TOP

There is an alternative to TOP, which is to use rowcount. Use rowcount with care, as it can lead you into all sorts of problems if it's not turned off.

SET rowcount 10
SELECT * from Customers
ORDER BY CompanyName


This is new to SQL Server 2005 and looks really useful. Below is an example to get records 20 to 29 from a results set. It might a bit strange at first, but I will go through the query so you'll see how simple it is.

With Cust AS
    ( SELECT CustomerID, CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
    FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

The WITH in SQL Server 2005 specifies a temporary named result, much like a temporary table in previous versions of SQL Server. However, the import parts are the ROW_NUMBER and the OVER statement, which create a row number on each row based on the Company name. This is like adding an identity seed to a temp table with an order by clause.

I hope you are still with me. If not, run the code and view the results. This is really very quick for large tables; I have been impressed with the speed on tables with over 250,000 records.

Putting it All Together in a Stored Procedure

Now we will put it all together in a Stored Procedure that can be used by your application. I won't show a .NET datagrid or similar control, as that is outside the scope of this article. The stored procedure below uses flexible page sizes and page numbers, so you can select any page at random. This is quite useful if you wish to jump ahead 10 pages to find a particular record. The paging for this example starts at page 1 rather than at page 0, but this can be easily changed.

CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 


Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end


To run this, simply specify the page size and page number (GetCustomersByPage, @PageSize and @PageNumber) as shown below.

exec GetCustomersByPage 10, 1


  • Version 1.0 22/03/2007: Initial
  • Version 1.1 18/04/2007: Fixed issue with proc that gets 11 records after the 1st page


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Frank Kerrigan
Software Developer (Senior)
United Kingdom United Kingdom
Frank Kerrigan

Currently developing Insurance systems with SQL Server, ASP.NET, C#, ADO for a company in Glasgow Scotland. Very keen on OOP and NUNIT testing. Been in IT forever (20 years) in mix of development and supporting applications / servers. Worked for companies big and small and enjoyed both.

Developed in (newest first) : C#, Progress 4GL, ASP.NET, SQL TSQL, HTML, VB.NET, ASP, VB, VBscript, JavaScript, Oracle PSQL, perl, Access v1-2000, sybase/informi, Pic Controllers, 6502 (ask your dad).

Msc .Net Development Evenings
MCAD Passed
MCP C# ASP.NET Web Applications
MCP SQL Server 2000
HND Computing
OND / HNC Electrical Engineering,

You may also be interested in...


Comments and Discussions

AnswerRe: old problem - my solution - this will work for SQL 2K and 2K5 Pin
Abishek Bellamkonda3-Apr-07 14:49
memberAbishek Bellamkonda3-Apr-07 14:49 
GeneralUsing Double Top Pin
helitb3-Apr-07 2:51
memberhelitb3-Apr-07 2:51 
GeneralRe: Using Double Top Pin
emission15-Aug-08 1:31
memberemission15-Aug-08 1:31 
GeneralOld Problem and old solutions : But Brillantly articulated Pin
Abishek Bellamkonda2-Apr-07 17:43
memberAbishek Bellamkonda2-Apr-07 17:43 
GeneralRe: Old Problem and old solutions : But Brillantly articulated Pin
Frank Kerrigan3-Apr-07 3:32
memberFrank Kerrigan3-Apr-07 3:32 
AnswerRe: Old Problem and old solutions : But Brillantly articulated [modified] Pin
edge94218-Apr-07 8:18
memberedge94218-Apr-07 8:18 
GeneralRe: Old Problem and old solutions : But Brillantly articulated Pin
Abishek Bellamkonda9-Apr-07 14:11
memberAbishek Bellamkonda9-Apr-07 14:11 
QuestionHow to get count of pages/records Pin
SimonS2-Apr-07 5:45
memberSimonS2-Apr-07 5:45 
It's all very well being able to page, but how do you get the total records?

I understand that count(*) is a performance killer, so the obvious answer of SELECT COUNT(*) FROM doesn't really apply.

It's the one thing about paging that no one's mentioned.



> company:: Broken Keyboards Software
> blog:: brokenkeyboards

> skype :: SimonMStewart
> CV :: PDF

AnswerRe: How to get count of pages/records Pin
LeeBear2-Apr-07 12:20
memberLeeBear2-Apr-07 12:20 
GeneralRe: How to get count of pages/records Pin
Leaper24-Apr-07 2:56
memberLeaper24-Apr-07 2:56 
GeneralRe: How to get count of pages/records Pin
LeeBear24-Apr-07 3:25
memberLeeBear24-Apr-07 3:25 
AnswerRe: How to get count of pages/records [modified] Pin
drkwtkns3-Apr-07 5:13
memberdrkwtkns3-Apr-07 5:13 
GeneralRe: How to get count of pages/records Pin
jexia16-May-07 11:22
memberjexia16-May-07 11:22 
GeneralRe: How to get count of pages/records Pin
Mexi-Fry2-Mar-08 14:16
memberMexi-Fry2-Mar-08 14:16 
GeneralAnd now for Joined tables Pin
Steve Phillips27-Mar-07 3:07
memberSteve Phillips27-Mar-07 3:07 
GeneralRe: And now for Joined tables Pin
Frank Kerrigan27-Mar-07 3:25
memberFrank Kerrigan27-Mar-07 3:25 
NewsAnother way Pin
Jeff X. Chang26-Mar-07 18:30
memberJeff X. Chang26-Mar-07 18:30 
GeneralRe: Another way Pin
grundt26-Mar-07 20:29
membergrundt26-Mar-07 20:29 
GeneralRe: Another way Pin
Colin Angus Mackay26-Mar-07 23:39
mvpColin Angus Mackay26-Mar-07 23:39 
GeneralRe: Another way Pin
Frank Kerrigan26-Mar-07 23:42
memberFrank Kerrigan26-Mar-07 23:42 
AnswerRe: Another way Pin
Timar Lucian2-Apr-07 21:08
memberTimar Lucian2-Apr-07 21:08 
GeneralRe: Another way Pin
Jeff X. Chang3-Apr-07 2:49
memberJeff X. Chang3-Apr-07 2:49 
GeneralRe: Another way Pin
Mexi-Fry2-Mar-08 14:37
memberMexi-Fry2-Mar-08 14:37 
GeneralUse in conjunction with TOP Pin
grundt26-Mar-07 15:24
membergrundt26-Mar-07 15:24 
GeneralRe: Use in conjunction with TOP Pin
Frank Kerrigan26-Mar-07 23:39
memberFrank Kerrigan26-Mar-07 23:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 19 Apr 2007
Article Copyright 2007 by Frank Kerrigan
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid