Paged Result Sets

What is the recommended mechanism for selecting paged results from SQL.

Presently I pass various params including the request Max Items Per Page and the requested page.

The I execute the query as a count with the search params.

Then comes the paging logic, which validates the page number against the request page and number of hits etc.

Then a temp table and record variables are created for the results.

Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).

Then I return the temp table & some additional return params with the Total Hits etc.

The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.

Thanks for any input,

Martin.

hi martin,

i think this is a front end issue not SQL's

vb.net objects like grids, detailsview and formview

supports paging builtin to them without the need of relying

to Sql server.

you can read the data into the dataset and present it with vb using

objects that support paging.

regards,

joey

|||

Hi Joey,

I am more than capable with ADO.Net. This is not a front end issue.

I have say 200,000 records and I want 15 items, page 30 in a result set displaying 15 items per page. I do not wish to return 450 items to a dataset or to a datareader that has to do multiple round trips to the server to get top the records I require.

I want the SPROC to return the 15 items that I am requesting. Do you understand what I am saying taking this in context with what I have said above?

I can presently do this. Though, I am looking for some one of you SQL Pro's to tell how I should be doing it.

|||

edited

hi martin,

sorry just clarifying. Anyway try this

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

exec pagemynorthwindorders 1

regards,

joey

|||

Hello again!

Looking at this this means that I am selecting all - say using Top 1000 - one thousand records into a temp table?

into #dummyorders I am unfamiliar with this syntax.

Inner queries and stuff! Is this more effeective than the Cursor approach? Is it possible you could break out the SPROC above a little with some comments.

It is much appreciated.

|||

no problem

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15 -- in case you want to change your paging size

-- create a temp table with its own identity column starting from 1

-- since order id has its own identity i need translate it to a string using str

-- there can only be one identity column in the table

-- i need only the pk (orderid) since it can identify the records i need

-- the #dummyorders record returns my own id_num and the orderid

-- id_num shall be used for paging , orderid is to identify what records

belong to the page

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders

-- this get the records from orderid that exist in the required page

select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

--(@.page-1)*@.pagesize+1 and (@.page)*@.pagesize

let say you want page 2 it gets the record between

-- (2-1)*15+1 and (2*15)

-- between 16 and 30 which is actually page 2

--at the end of the procedure #dummyorders destroys itself

exec pagemynorthwindorders 1

|||

final query, just improve it to suite you needs

use northwind

alter proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15
select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize+1 and (@.page)*@.pagesize
)

go

exec pagemynorthwindorders 2

|||

This is a very common question and there are actually many good answers.

If you are using SQL2005 there are two really cool features you could use:

- the tops now accept a variable: select top @.variable

- the ROW_NUMBER function, that creates an additional column with the ID of the row

Try this:

declare @.ipagesize as int,

@.ipage as int

set @.ipagesize = 5

set @.ipage = 0

select top ( @.ipagesize ) *

from

(

select ROW_NUMBER () over ( order by orderID ) as row_order , *

from orders

)

ordered_set

where ordered_set.row_order > @.ipagesize * @.ipage

|||

Thanks people,

Both are cleaner than my current solution.

Though, is there a way to get an estimate of the total hits from the same statement or must I use an additional count?

Also, I know there is a knew Table or .Table statement for SQL 2005 which I thought may have popped up in the answers. The Top Param non variable limitation was an anoying so that is also valuable info.

|||new!