SQL Clone
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
Adam Haines
Adam Haines
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9580 Visits: 3135
Comments posted to this topic are about the item Custom Pagination in SQL Server 2005



My blog: http://jahaines.blogspot.com
pp.pragnesh
pp.pragnesh
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 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
------------------------------------------
David Dye
David Dye
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 308
Great work Adam. Clear, concise, and detailed.

Thanks
cetin
cetin
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 27
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.
jctrelfa
jctrelfa
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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)
thierry.vandurme
thierry.vandurme
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 566
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?
Adam Haines
Adam Haines
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9580 Visits: 3135
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
Adam Haines
Adam Haines
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9580 Visits: 3135
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
cetin
cetin
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 27
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.
peter-757102
peter-757102
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2543 Visits: 2559
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!
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