January 6, 2009 at 12:07 am
Comments posted to this topic are about the item Custom Pagination in SQL Server 2005
January 6, 2009 at 2:35 am
Here is another simple SP for Paging that works even in SQL-2k.
------------------------------------------
CREATE Procedure usp_GetPagedOrders
@PageIndex integer,
@PageSize integer
---*** HOW TO USE USE *** ----
--- EXEC usp_GetPagedOrders 10, 25
--- Parameters Defination:
--- @PageIndex --> Page Number which you want to show
--- @PageSize --> List of Records in each Page
---*** HOW TO USE USE *** ----
AS
Declare @StartID integer,
@EndID integer
Declare @Temp table (
ID integer IDENTITY(1, 1) NOT NULL,
OrderID integer NOT NULL,
CustomerID nchar(5) NULL,
OrderDate datetime NULL,
Freight money NULL)
SET NOCOUNT ON
Insert @Temp (
OrderID,
CustomerID,
OrderDate,
Freight)
Select OrderID,
CustomerID,
OrderDate,
Freight
From Orders
Set @StartID = @PageIndex * @PageSize + 1
Set @EndID = (@PageIndex + 1) * @PageSize
Select OrderID,
CustomerID,
OrderDate,
Freight
From @Temp
Where ID Between @StartID And @EndID
GO
------------------------------------------
January 6, 2009 at 5:42 am
Great work Adam. Clear, concise, and detailed.
Thanks
January 6, 2009 at 5:45 am
I think the code in that article (the one that uses row_number) has one problem. What if:
User A retrieves first page (rows 1-25 in name order)
User B deletes one or more rows that user A retrieved
User A requests next page (she thinks she is getting 26-50 in name order, and she does, but now some names are shifted to page 1 due to deletion and she missed them)
The other code here that uses persisted identity column is better IMHO.
January 6, 2009 at 5:46 am
Please forgive my ignorance - but this seems like a lot of extra overhead:
DECLARE @max_id INT
SET @max_id = (SELECT max(contactid) FROM [Adventureworks].[Person].[Contact])
INSERT INTO Contacts
SELECT TOP 1000000 --<<<--THE NUMBER OF ROWS!
FirstName = (SELECT TOP 1 FirstName
FROM adventureworks.person.contact
WHERE ContactID = ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1),
LastName = (SELECT TOP 1 LastName
FROM adventureworks.person.contact
WHERE ContactID = ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1)
FROM
Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
GO
Is there a downside to a single update/select without all the additional subqueries and functions you have in the article?
INSERT INTO Contacts (FirstName, LastName)
SELECT TOP 100000
FirstName,
LastName
FROM [Adventureworks].[Person].[Contact]
Maybe there's something that I just don't understand (I'm relatively new to this stuff)
January 6, 2009 at 5:54 am
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?
January 6, 2009 at 5:55 am
jctrelfa (1/6/2009)
Please forgive my ignorance - but this seems like a lot of extra overhead:
DECLARE @max_id INT
SET @max_id = (SELECT max(contactid) FROM [Adventureworks].[Person].[Contact])
INSERT INTO Contacts
SELECT TOP 1000000 --<<<--THE NUMBER OF ROWS!
FirstName = (SELECT TOP 1 FirstName
FROM adventureworks.person.contact
WHERE ContactID = ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1),
LastName = (SELECT TOP 1 LastName
FROM adventureworks.person.contact
WHERE ContactID = ABS(CONVERT(BINARY(6),NEWID()) % @max_id) + 1)
FROM
Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
GO
Is there a downside to a single update/select without all the additional subqueries and functions you have in the article?
INSERT INTO Contacts (FirstName, LastName)
SELECT TOP 100000
FirstName,
LastName
FROM [Adventureworks].[Person].[Contact]
Maybe there's something that I just don't understand (I'm relatively new to this stuff)
Your right.. the insert code does use a lot of additional overhead, but for good reason. The code randomizes first and last names from the AdventureWorks database, based on first and last names that are preexisting. This way we can generate a lot more random data, thus making indexing better and the data more accurate. The method you proposed will only dump the number of records from adventureworks, as they exist which is less than 50,000 rows.
January 6, 2009 at 6:06 am
cetin (1/6/2009)
I think the code in that article (the one that uses row_number) has one problem. What if:User A retrieves first page (rows 1-25 in name order)
User B deletes one or more rows that user A retrieved
User A requests next page (she thinks she is getting 26-50 in name order, and she does, but now some names are shifted to page 1 due to deletion and she missed them)
The other code here that uses persisted identity column is better IMHO.
Cetin,
You bring up a very valid point, but you introduce a new problem... Sequence gaps. Perhaps the user will be even more concerned why row 25 does not exist on any page, if deleted. Using the posted method allows the user to get a realistic look at the data, as it exists in the database.
Is using Row_Number pagination always the best solution... no. The best solution depends on your environment and your data. The article provides easy to setup and performant means to accomplish pagination in SQL 2005.
Edit: My response was to using a table identity column. I just reviewed the posted code above and the would work the same way as the row number solution posted. The difference here is you CANNOT gaurentee the sequencing of the data inserted with the INSERT INTO statement. This in itself can throw off your sequencing.
January 6, 2009 at 6:34 am
You are right about the code that uses Identity column. I missed that Identity column was created in a stored procedure. I meant identity that is persisted in original table.
W/o a persisted column like identity I was thinking along:
--@lastRetrieved varchar(50) - last lastName we have retrieved in previous call
select top 25 * from contacts
where lastName > @lastRetrieved
order by lastName
PS: Not as easy as this one, extremely rough thinking loud.
January 6, 2009 at 7:03 am
First impression....a lot of code (and a lot of work).
I do not have the time to read 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!
January 6, 2009 at 7:05 am
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"?
January 6, 2009 at 7:23 am
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()'
if @SortClause <>''
SET @sql = @sql + ' AS Row, * '
SET @sql = @sql + ' FROM ' + @TableName
SET @sql = @sql + ' ' + @WhereClause
SET @sql = @sql + 'SELECT * FROM Records WHERE Row BETWEEN ' + @startrange + ' AND ' + @endrange + ' ' + @SortClause
--print @sql
EXEC (@SQL);
January 6, 2009 at 7:35 am
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.
January 6, 2009 at 7:39 am
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.
January 6, 2009 at 7:50 am
David Dye (1/6/2009)
Great work Adam. Clear, concise, and detailed.Thanks
Thanks David 🙂
Viewing 15 posts - 1 through 15 (of 68 total)
You must be logged in to reply to this topic. Login to reply