Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Custom Pagination in SQL Server 2005

By Adam Haines,

Introduction

Pagination is not a new concept to developers or users.  In fact, most of us use pagination every day.  Pagination gives users the ability to view data in small subsets, much like flipping through the pages of a book.  In fact, many development tools have built-in pagination; however, I believe we sometimes become too dependent on canned code.  It seems that managers today give less thought to scalability and performance and more thought to faster development. I am not saying that auto generated code is bad, but it is fair to say auto generated code is not always the most efficient.  Sometimes this inefficiency can directly impact user experience and the overall perspective of application performance. Pagination is one of these inefficiencies.  While it is easier to click the check box that says pagination, it is also a lot more costly. 

The Problem

The biggest problem with built-in pagination is the amount of data returned to the application.  Built-in pagination retrieves all the data from a subsequent query and performs filtering at the application tier, not the database tier. This leads to greater overhead and resource consumption, with no additive benefit.  For example, if you have 100,000 rows returned by a SQL query and the user wants to view 10 rows at a time, built-in pagination will return 100,000 rows regardless of the number of rows displayed on the screen.  From a user’s perspective, the load time should be fast because the application is only returning 10 rows.  When load times are slow for 10 rows, users begin to get frustrated and scream that the application is slow.  Users do not care, nor should they, what is going on in the background.  Users care about two things: usability and performance.  This article addresses the database logic that can be used to successfully implement a custom paging solution, which can translate into faster load times and a better user experience.

The Solution

SQL 2005 introduced new functionality called Row_Number.  Row_Number makes pagination a breeze because it gives developers the ability to assign a sequential number to each row returned.  The numbering is dictated by the partition and ordering set within the function.  This functionality allows developers to return only the data that is needed by the application, nothing more nothing less.   By returning less data to the application, we can directly increase performance and put less stress on the server.

Let’s start by creating a sample table and some data. 

Note: I created test data by populating empty tables with Adventure Works data.  You can download a free copy of the AdventureWorks database, on the codeplex website. http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

 

 

USE [tempdb]

GO

 

IF EXISTS(SELECT 1 FROM sys.tables WHERE [name] = 'Contacts')

BEGIN

DROP TABLE Contacts

END

GO

 

CREATE TABLE Contacts(

ContactID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

FirstName VARCHAR(25),

LastName VARCHAR(25)

)

GO

 

CREATE INDEX IXC_LastName ON dbo.Contacts(LastName) INCLUDE(FirstName)

GO

CREATE INDEX IXC_FirstName ON dbo.Contacts(FirstName) INCLUDE(LastName)

GO

 

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

 

Now that we have our base table and data, we can begin to discuss how to best accomplish the task at hand.  I am sure that most will need more functionality than paging data, so I have included dynamic sorting and filtering functionality. Usually, when someone hears the word dynamic they automatically think dynamic SQL; however, I will demonstrate some ways to achieve dynamic functionality, without the use of dynamic SQL.  I will also show how to achieve the same functionality through the use of dynamic SQL.

Note:  I will not go into any great detail about the performance benefits/costs and potential hazards of using dynamic SQL, but will show you some techniques to minimize risk. For more information about the use of dynamic sql, please read the following article by Erland Sommarskog, http://www.sommarskog.se/dynamic_sql.html.  

CREATE PROCEDURE usp_ContactPaging

(

      @SortCol VARCHAR(25)='ContactId ASC',

      @FirstName VARCHAR(25)=NULL,

      @LastName VARCHAR(25)=NULL,

      @pgSize INT=25, @pgNbr INT=1

)

AS

BEGIN

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE @NbrPages INT

 

IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

END

 

IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName

END

 

IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [LastName] = @LastName

END

 

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName AND

        [LastName] = @LastName

END

 

;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

AS

(

SELECT

      ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

      ContactId,

      FirstName,

      LastName

FROM Contacts

WHERE [FirstName] = COALESCE(@FirstName,FirstName) AND

      [LastName] = COALESCE(@LastName,LastName)

)

SELECT

      Row_ID,

      ContactId,

      FirstName,

      LastName,

@pgNbr AS PageNumber,

      @NbrPages AS TotalNbrPages

FROM PagingCTE

WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

      Row_ID <= @pgSize * @pgNbr

END

GO

 

This stored procedure is using a CTE (Common Table Expression) to create row numbering.  The variables are sort column, first name, last name, page number and page size.  The variable names are self explanatory and I need not go into detail.  The where clause uses a formula based on page size and the page number to calculate which rows should be returned to the application.  By submitting values such as @pgSize = 25, @pgNbr = 1, we are telling the procedure to return the 1st page and each page will contain 25 rows.

 One of the key things to note in the above stored procedure is the order by clause. The order by clause sorts the data depending on the parameter passed in.  As you can see, I used a case expression to filter the data conditionally based on the value of @SortCol.  We can use this method to dynamically sort, without the use of dynamic sql.  This still gives us good performance, costs little overhead and does not subject us to injection attack.  The predicate is built dynamically using COALESCE.  I am using the COALESCE function to set the column equal to itself, unless a value has been passed into the stored procedure.  Often developers will use (WHERE FirstName = @FirstName OR @FirstName IS NULL) instead.  This is essentially the same thing and should not result in a differing execution plan.   Can we all agree that this stored procedure looks good?  Well I disagree.  I used COALESCE to demonstrate what not to do when dealing with dynamic filters.  The method described above guarantees that the query will result in a table scan and that no indexes will be used.  I know this is outside the scope of the article, but should be mentioned.  The idea is to make data retrieval faster, not slower. 

Have your doubts?  Let’s test the stored procedure.  Note: Since we are dynamically generating table data, I cannot guarantee you will have the same first and last names in your table.  Supply a name that is valid in your table.

EXEC usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Results:

Execution Plan:

What can we do to make this query index seek?  Well, we really only have two options.  One is to use dynamic sql to build the predicate dynamically.  Two is to introduce control flow logic and have differing statements for each possibility.  I know that using control flow logic can be tedious and will make the procedure more difficult to manage, but I still prefer to use this method where possible.  For those in a situation where there are too many variables to use control flow logic, I will be demonstrating a dynamic sql method a bit later in the article.

Revised Stored Procedure

ALTER PROCEDURE usp_ContactPaging

(

      @SortCol VARCHAR(25)='ContactId ASC',

      @FirstName VARCHAR(25)=NULL,

      @LastName VARCHAR(25)=NULL,

      @pgSize INT=25, @pgNbr INT=1

)

AS

BEGIN

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE @NbrPages INT

 

IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

END

 

IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName

END

 

IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [LastName] = @LastName

END

 

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName AND

        [LastName] = @LastName

END

 

--NO filters, this will always TABLE/INDEX scan

IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr AS PageNumber,

            @NbrPages AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID <= @pgSize * @pgNbr

END

 

--FIRST NAME ONLY

IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [FirstName] = @FirstName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

@pgNbr AS PageNumber,

            @NbrPages AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID <= @pgSize * @pgNbr

END

 

--LAST NAME ONLY

IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [LastName] = @LastName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr AS PageNumber,

            @NbrPages AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID <= @pgSize * @pgNbr

END

 

--FIRST AND LAST NAME

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

 

      ;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

      AS

      (

      SELECT

            ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @SortCol='FirstName DESC' THEN FirstName END DESC,

             CASE WHEN @SortCol='FirstName ASC'  THEN FirstName END ASC,

             CASE WHEN @SortCol='LastName ASC'   THEN LastName  END ASC,

             CASE WHEN @SortCol='LastName DESC'  THEN LastName  END DESC,

             CASE WHEN @SortCol='ContactID ASC'  THEN ContactId END ASC,

             CASE WHEN @SortCol='ContactID DESC' THEN ContactId END DESC

            ) AS [Row_ID],

            ContactId,

            FirstName,

            LastName

      FROM Contacts

      WHERE [FirstName] = @FirstName AND

            [LastName] = @LastName

      )

      SELECT

            Row_ID,

            ContactId,

            FirstName,

            LastName,

            @pgNbr AS PageNumber,

            @NbrPages AS TotalNbrPages

      FROM PagingCTE

      WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND

            Row_ID <= @pgSize * @pgNbr

END

 

END

GO

As you can see, the stored procedure just got a lot uglier and longer; however, the optimizer can now generate a quality execution plan.  Execute the new procedure, using the same variables.

EXEC usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Query Results:

Execution Plan:

Note:  If you use the LIKE operator with begins with, you may see a nested loop join instead of a single seek.  Like uses a nested loop join to compare a constant value (your parameter value) to an underlying table, in our case contacts.  This type of join will still yield good performance for our purposes because we are only seeking the table one time; however, this type of join is usually slower for large table joins.

By adding control flow logic to the existing stored procedure we are able to index seek.   The major problem with this method is maintainability becomes increasingly difficult; however, I believe manageability takes a backseat to security.  Next I will demonstrate the parameterized dynamic sql method.

The biggest consideration when building a dynamic SQL query is how to minimize risk and parameterize the statement. In order to parameterize dynamic SQL you must execute dynamic SQL using the system stored procedure sp_executesql.    Parameterized dynamic SQL is less susceptible to injection attack, in comparison to non parameterized dynamic SQL.  Parameterized dynamic SQL also has the distinct advantage of query plan reuse. The optimizer does not usually allow for query plan reuse with non parameterized dynamic SQL, unless an identical query plan already exists.  For parameterized dynamic SQL, the optimizer is able to keep parts of an existing query plan and interchange values, without having to generate a new plan. 

Parameterized Dynamic SQL:

ALTER PROCEDURE usp_ContactPaging

(

      @SortCol VARCHAR(25)='ContactId ASC',

      @FirstName VARCHAR(25)=NULL,

      @LastName VARCHAR(25)=NULL,

      @pgSize INT=25, @pgNbr INT=1

)

AS

BEGIN

 

DECLARE @SQL nvarchar(max),

        @params nvarchar(100)

 

/*==================================================

 I use the below code to get the total number of

 rows. If you do not need this functionality,

 you can remove this section of code and remove

 @NbrPages in the SELECT statements.

==================================================*/

DECLARE @NbrPages INT

 

IF @FirstName IS NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

END

 

IF @FirstName IS NOT NULL AND @LastName IS NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName

END

 

IF @FirstName IS NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [LastName] = @LastName

END

 

IF @FirstName IS NOT NULL AND @LastName IS NOT NULL

BEGIN

SELECT @NbrPages =

            CEILING(count(*)/(@pgSize*1.0))

FROM Contacts

WHERE [FirstName] = @FirstName AND

        [LastName] = @LastName

END

 

SET @params = N'@SIZE INT, @nbr INT, @Pages INT, @Sort VARCHAR(25)'

SET @SQL = N'

;WITH PagingCTE (Row_ID,ContactId,FirstName,LastName)

AS

(

SELECT

--YOU CAN REMOVE THE CASE AND USE THE SORT VARIABLE HERE BUT IT

--INCREASES SUSCEPTIBILITY

      ROW_NUMBER()

            OVER(ORDER BY

             CASE WHEN @Sort=''FirstName DESC'' THEN FirstName END DESC,

             CASE WHEN @Sort=''FirstName ASC''  THEN FirstName END ASC,

             CASE WHEN @Sort=''LastName ASC''  THEN LastName  END ASC,

             CASE WHEN @Sort=''LastName DESC''  THEN LastName  END DESC,

             CASE WHEN @Sort=''ContactID ASC''  THEN ContactId END ASC,

             CASE WHEN @Sort=''ContactID DESC'' THEN ContactId END DESC

            ) AS [Row_ID],

      ContactId,

      FirstName,

      LastName

FROM Contacts

' + CASE

      WHEN @FirstName IS NOT NULL AND @LastName IS NULL THEN

      N'WHERE FirstName = ' + QUOTENAME(@FirstName,'''')

      WHEN @FirstName IS NULL AND @LastName IS NOT NULL THEN

      N'WHERE LastName = ' + QUOTENAME(@LastName,'''')

      WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL THEN

      N'WHERE FirstName = ' + QUOTENAME(@FirstName,'''') + ' AND ' +    N'LastName = ' + QUOTENAME(@LastName,'''') 

      END + N'

)

SELECT

      Row_ID,

      ContactId,

      FirstName,

      LastName,

      @Nbr AS PageNumber,

      @Pages AS TotalNbrPages

FROM PagingCTE

WHERE Row_ID >= (@SIZE * @nbr) - (@SIZE -1) AND Row_ID <= @SIZE * @nbr'

 

--PRINT @sql

 

EXEC sp_executesql

      @SQL,

      @params,

      @SIZE = @pgSize,

      @nbr = @pgNbr,

      @Pages = @NbrPages,

      @Sort = @SortCol

 

END

GO

Now let’s execute the stored procedure

EXEC usp_contactpaging 'contactid DESC','Adam',NULL,25,1

GO

Query Results:

Execution Plan:

Note: You can remove the Row_Number case statement and use @SortCol to build the order by clause dynamically; however, you will be subject to greater security risk and will not greatly increase performance.

Conclusion

In our attempt to cut corners to meet deadlines, we sometimes miss the key ingredients to making an application as efficient as possible.  One of these corners is built-in pagination.  While it is much easier to check the box for pagination, it does not mean this is the best way to introduce pagination to your environment.  SQL 2005 has given us a simplistic method to create custom pagination.  The methodology is possible because of a new function called Row_Number.    By implementing custom pagination functionality, we can directly increase user satisfaction, have faster page loads, and reduce amount of data returned to the application.

Links:

·         Adventure Works DB - http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

·         Dynamic SQL – http://www.sommarskog.se/dynamic_sql.html

·         Row_Number() - http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx

·         Sp_executesql - http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

Total article views: 9756 | Views in the last 30 days: 2
 
Related Articles
FORUM

how to devied fullname into firstname and lastname

how to devied fullname into firstname and lastname

FORUM

Lastname, Firstname switch

I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION,...

FORUM

Split out FirstName, LastName, MiddleName

One column name as [MemberName] in which stored data like LastName, FirstName M (with initial mi...

FORUM

Create FirstName and LastName to Replace Existing FirstName and LastName

Hello Everyone I hope that you all are having a very nice day. I am wanting to change all the Fi...

FORUM

Script for firstname first letter and lastname

Hi, I have a column with firstname lastname say 'john fernandez' i have to get jferandez..Please h...

Tags
paging    
row_number()    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones