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 12:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
Comments posted to this topic are about the item Custom Pagination in SQL Server 2005



My blog: http://jahaines.blogspot.com
Post #630336
Posted Tuesday, January 6, 2009 2:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 15, 2011 11:15 PM
Points: 224, Visits: 53
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
------------------------------------------
Post #630384
Posted Tuesday, January 6, 2009 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:12 PM
Points: 7, Visits: 250
Great work Adam. Clear, concise, and detailed.

Thanks
Post #630468
Posted Tuesday, January 6, 2009 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 10, 2013 9:11 AM
Points: 11, Visits: 25
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.
Post #630470
Posted Tuesday, January 6, 2009 5:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 3, 2009 6:29 AM
Points: 1, Visits: 10
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)
Post #630472
Posted Tuesday, January 6, 2009 5:54 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:33 AM
Points: 710, Visits: 401
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?
Post #630479
Posted Tuesday, January 6, 2009 5:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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.




My blog: http://jahaines.blogspot.com
Post #630481
Posted Tuesday, January 6, 2009 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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.




My blog: http://jahaines.blogspot.com
Post #630486
Posted Tuesday, January 6, 2009 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 10, 2013 9:11 AM
Points: 11, Visits: 25
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.
Post #630508
Posted Tuesday, January 6, 2009 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 328, Visits: 2,234
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!
Post #630532
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse