Custom Pagination in SQL Server 2005

  • Adam Haines

    SSC-Insane

    Points: 23197

    Comments posted to this topic are about the item Custom Pagination in SQL Server 2005

  • pp.pragnesh

    Old Hand

    Points: 330

    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

    SSC Veteran

    Points: 273

    Great work Adam. Clear, concise, and detailed.

    Thanks

  • cetin

    SSC Enthusiast

    Points: 185

    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

    SSC Journeyman

    Points: 77

    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

    Hall of Fame

    Points: 3282

    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

    SSC-Insane

    Points: 23197

    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.

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • cetin

    SSC Enthusiast

    Points: 185

    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

    SSCertifiable

    Points: 6877

    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!

  • tymberwyld

    SSCertifiable

    Points: 7810

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

  • souplex

    SSC Enthusiast

    Points: 191

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

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720940

    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.

  • Adam Haines

    SSC-Insane

    Points: 23197

    David Dye (1/6/2009)


    Great work Adam. Clear, concise, and detailed.

    Thanks

    Thanks David 🙂

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply