Custom Pagination in SQL Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by adam haines » Custom Pagination in SQL Server 2005

69 posts, Page 2 of 7

««1 2 3 4 5 »»»

Custom Pagination in SQL Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

tymberwyld

Posted Tuesday, January 06, 2009 7:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268

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"?

Post #630535

souplex

Posted Tuesday, January 06, 2009 7:23 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 6:02 AM
Points: 13, Visits: 50

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);

Post #630556

Adam Haines

Posted Tuesday, January 06, 2009 7:35 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:18 AM
Points: 2,278, Visits: 2,995

tymberwyld (1/6/2009)

thierry.vandurme (1/6/2009)

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

Post #630569

Steve Jones - SSC Editor

Posted Tuesday, January 06, 2009 7:39 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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

Post #630576

Adam Haines

Posted Tuesday, January 06, 2009 7:50 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:18 AM
Points: 2,278, Visits: 2,995

David Dye (1/6/2009)

Great work Adam. Clear, concise, and detailed.

Thanks

Thanks David :)

My blog: http://jahaines.blogspot.com

Post #630587

Adam Haines

Posted Tuesday, January 06, 2009 8:01 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:18 AM
Points: 2,278, Visits: 2,995

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 :). 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

Post #630595

ryan.mcatee

Posted Tuesday, January 06, 2009 8:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 11:07 AM
Points: 43, Visits: 118

"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.

Post #630608

rakhi.bhatia

Posted Tuesday, January 06, 2009 8:27 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:13 PM
Points: 78, Visits: 137

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

Post #630619

turkbuku

Posted Tuesday, January 06, 2009 8:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 24, 2009 2:50 AM
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..

Post #630622

Adrian Hains

Posted Tuesday, January 06, 2009 8:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 01, 2012 1:18 PM
Points: 110, 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.

Post #630641

« Prev Topic | Next Topic »

69 posts, Page 2 of 7

««1 2 3 4 5 »»»

Permissions