Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Custom Pagination in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, January 6, 2009 7:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 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
Posted Tuesday, January 6, 2009 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 27, 2014 9:54 PM
Points: 13, Visits: 63
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
Posted Tuesday, January 6, 2009 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 2,278, Visits: 3,061
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
Post #630569
Posted Tuesday, January 6, 2009 7:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 31,168, Visits: 15,612
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
Posted Tuesday, January 6, 2009 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 2,278, Visits: 3,061
David Dye (1/6/2009)
Great work Adam. Clear, concise, and detailed.

Thanks

Thanks David :)




My blog: http://jahaines.blogspot.com
Post #630587
Posted Tuesday, January 6, 2009 8:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 2,278, Visits: 3,061
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
Posted Tuesday, January 6, 2009 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
"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
Posted Tuesday, January 6, 2009 8:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:05 AM
Points: 79, Visits: 185
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
Posted Tuesday, January 6, 2009 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 6, 2009 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse