Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Custom Pagination in SQL Server 2005


Custom Pagination in SQL Server 2005

Author
Message
tymberwyld
tymberwyld
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 274
thierry.vandurme (1/6/2009)
I don't really understand how this reduces server resource utilization except for network traffic. If a user is paging through the data screen, only a screen full of rows (10,20...) will transmit over the network but the query will be executed each time the user clicks on page down. Maybe I'm missing something?

Simple, most Pagination routines are processed on the client. What Adam is getting at here is that instead of sending all 1,000,000 rows back through the Network to the Client for every, single Page a user clicks on, you are only sending back the rows the user is requesting. This not only reduces the load on your server but not to mention your application servers (mostly IIS hosting web sites).

Adam, the only additional thing I didn't see was how to Sort by multiple columns. What if I want "LastName ASC, FirstName DESC"?



souplex
souplex
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 75
Created this one a while back and still use it. You might want to give it a try.
It aint as fancy as yours, but it will save you a lot of maintenance.

Since the user most of the time dictates the sortorder
and the where clause I prefer passing these in from the outside.
You can simply leave them blank where you don't need them.


CREATE procedure [dbo].[proc_GetSortedPage]
(
@TableName VARCHAR(500),
@SortClause VARCHAR(4000),
@WhereClause VARCHAR(4000),
@Pagesize int,
@Pageindex int
)
as

-- @TableName: name of table or view
-- @SortClause: the sort clause without the ORDER BY statement, but including ASC or DESC
-- @WhereClause: the where clause, without the WHERE statement
-- @Pagesize: the size of the page
-- @Pageindex: the number of the page you want to return, starts counting at 1

IF @SortClause IS NULL
set @SortClause = ''

set @SortClause = LTRIM(@SortClause)
set @SortClause = RTRIM(@SortClause)

if @SortClause <>''
set @SortClause = ' ORDER BY ' + @SortClause

IF @WhereClause IS NULL
set @WhereClause = ''

set @WhereClause = LTRIM(@WhereClause)
set @WhereClause = RTRIM(@WhereClause)

if @WhereClause <>''
set @WhereClause = ' WHERE ' + @WhereClause


if @pagesize is NULL
set @pagesize = 100

if @pagesize < 1
set @pagesize = 100

if @pageindex is null
set @pageindex = 1

if @pageindex < 1
set @pageindex = 1

declare @startrange varchar(15)
declare @endrange varchar(15)

set @startrange = cast((((@pageindex-1) * @pagesize) + 1) as varchar(15))
set @endrange = cast((@pageindex * @pagesize) as varchar(15))


DECLARE @SQL VARCHAR(8000)

SET @SQL = 'WITH Records AS ('
SET @SQL = @SQL + 'SELECT ROW_NUMBER()'
SET @SQL = @SQL + ' OVER ('
if @SortClause <>''
SET @SQL = @SQL + @SortClause
SET @SQL = @SQL + ') '
SET @SQL = @SQL + ' AS Row, * '
SET @SQL = @SQL + ' FROM ' + @TableName
SET @SQL = @SQL + ' ' + @WhereClause
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT * FROM Records WHERE Row BETWEEN ' + @startrange + ' AND ' + @endrange + ' ' + @SortClause

--print @SQL
EXEC (@SQL);



Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
tymberwyld (1/6/2009)
thierry.vandurme (1/6/2009)
I don't really understand how this reduces server resource utilization except for network traffic. If a user is paging through the data screen, only a screen full of rows (10,20...) will transmit over the network but the query will be executed each time the user clicks on page down. Maybe I'm missing something?

Simple, most Pagination routines are processed on the client. What Adam is getting at here is that instead of sending all 1,000,000 rows back through the Network to the Client for every, single Page a user clicks on, you are only sending back the rows the user is requesting. This not only reduces the load on your server but not to mention your application servers (mostly IIS hosting web sites).

Adam, the only additional thing I didn't see was how to Sort by multiple columns. What if I want "LastName ASC, FirstName DESC"?


Unfortunately, you will have to use a dynamic SQL to sort by more than one column. You will need to use the SortCol variable in place of the case expression. You will also need to remove the parameterized @sort column form the sp_executesql command.

Here is a sample:

ROW_NUMBER() OVER(ORDER BY ' + @SortCol + ') AS [Row_ID],



Forgot to mention that doing this will greatly reduce parameterization optimization and open up a security hole. Make sure to validate all input variables for malicious intent.



My blog: http://jahaines.blogspot.com
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36344 Visits: 18752
Adam,

Nice job and a good solution to the problem. There are lots of these, but it's good to be reminded of how this works as many people ask for all rows and then figure out which ones of the thousands returned they display.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
David Dye (1/6/2009)
Great work Adam. Clear, concise, and detailed.

Thanks

Thanks David Smile



My blog: http://jahaines.blogspot.com
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
peter (1/6/2009)
First impression....a lot of code (and a lot of work).

I do not have the time to read it the lengthy article in full but as the OP rightly articulated, the problem is well known and has a myriad of (often half) solutions. This means I made one for myself not too long ago that supports some extra twists and does not look as complicated while performing well for the datasets it was meant to.

I will make a proper contributing post and share the code tomorrow, right now I have a deadline to meet, so stay tuned!

I know this article is quite a mammoth Smile. I tried to trim the article as much as possible, but there is just too much information to cover. Thank you for taking the time to look over the article.

I look forward to reading your post.

Thanks,
Adam



My blog: http://jahaines.blogspot.com
ryan.mcatee
ryan.mcatee
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 134
"Row_Number makes pagination a breeze..."

Um.

With all due respect to the author (and I truly mean that. It's a great article.) but was this statement a joke? I wouldn't exactly call this a "breeze", but maybe that's because I'm not near the expert that this guy is.
rakhi.bhatia
rakhi.bhatia
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 224
I have a question about the dynamic SQL block of code you have included in your article.

So the way you have constructed your dynamic SQL, you took @FirstName and @LastName and converted them to their actual values and included them in your @SQL variable.

Shouldn't you rather have used @FirstName and @LAstNAme as parameters in @SQL so that the query execution plan will be reused for all combinations of @FirstName and @LAstNAme ? Otherwise, each time that a new value is passed for @FirstName and @LastNAme, a new execution plan will be generated.

What I mean is this..

Instead of :
Set @SQL = 'select....where FirstNAme=' + Quotename(@firstname,'''') + ' and LastName=' + QuoteName(@lastname,'''')

Use:
Set @SQL = 'Select ... Where FirstName=@FirstName And LastName=@LastName'
and pass the parameters for @FirstName and @LastName

turkbuku
turkbuku
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
CREATE PROCEDURE [dbo].[Sp_Customer_Paging]
@PageSize INT=50,
@PageNumber INT=1


AS
Declare
@SQL varchar(4000),
@FirstRow INT,
@LastRow INT,
@TmpSQLDetail1 varchar(4000),
@TmpSQLDetail2 varchar(4000)

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1, @LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;


Set @SQL =' WITH Members AS
( SELECT Cust_No,CardNo,CustType,
ROW_NUMBER() OVER (ORDER BY vw_customers.Cust_No) AS RowNumber
FROM vw_customers WHERE 1=1 '

set @SQL = @SQL + ' ),
ListRecordCount AS ( SELECT Cust_No,CardNo,CustType,(SELECT MAX(RowNumber) AS RecordCount FROM Members)as RCount FROM Members)'

set @SQL = @SQL + ' SELECT *'
+' FROM ListRecordCount'
+' WHERE RowNumber BETWEEN '+ CAST(@FirstRow AS VARCHAR(10)) +' AND '+cast(@LastRow as VARCHAR(10))+' '
+' ORDER BY RowNumber ASC';






EXEC(@SQL)


--sample calling store procedure exec Sp_Customer_Paging 100,1
--result will first 100 rows with RCount you can arrange number of page with rcount/100
--rcount=500 means you have 5 page when you call Sp_Customer_Paging 100,3 it will returns between 200 and 300 records etc..
Adrian Hains
Adrian Hains
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 261
I prefer keeping the notion of paging in the client space, in my opinion it is a cleaner API to use something like System.IO.StreamReader and provide input params defining the start row and number of rows. Treat the db basically like a stream or buffer. You won't find an API in the platform that has inputs numItemsPerPage, numPages.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search