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

Server Side Paging With SQL Server 2005

By Jacob Sebastian,

Introduction

Most of the web developers must have come across the requirement to implement Server Side Paging of the data to increase the performance of the application. In the absence of Server Side Paging, the application will fetch all the data from the database server and load a specific number of records (depending on the current page being viewed by the user). Assume that a table has 10,000 records and the paging used by the application is 50. When the user clicks on Page 2, the application will fetch all the 10,000 records from the database server and then load records 51 to 100 to the UI control on the web page. This shows that, we are fetching a lot of records which we really do not use. By fetching only the records that we need (in the above example, records 51 from 100) from the database server, we can gain better performance at the database server level as well as at the application level.

There are quite a few articles available on Internet which address this problem from different angles. Some of the interesting articles that I could find are the following:

None of the articles I could find online was considering all the requirements that I was looking for. I wanted that the Server Side Paging code should consider the following points:

  • Select the required number of records based on the current page count and the page size. If the page size is 25 records and if we are on page 4, then we need to retrieve records from 76 to 100.
  • The sort order needs to be handled. The data that we need to retrieve for page 4 will be different when the sort order changes. For example, when the sort order is First Name a different set of records are to be returned than City.
  • Filters need to be applied in the TSQL code. Most of the times, the data is retrieved against a search operation which takes various filter values. For example, the Employee search might take filters like First Name, City or Hire Date. It could also be that, the filters are optional. None, one, many or all of the filters can be specified in the query. If a filter is provided, then the data needs to be filtered for that condition. Otherwise, that filter should be ignored.

At this point, I thought of writing my own version of the Server Side Paging TSQL code which takes care of all the points mentioned above.

Sample Code

We will use the NorthWind database for the purpose of this example. The following are the requirements that this example will fulfill.  

  1. A web page needs to be created for displaying a list of Employees
  2. User can search by First Name, Title and City
  3. User can enter None, One, Two or All of the filters
  4. We will use LIKE matching while applying the filters
  5. The page should display only 10 records at a time. Paging should be implemented for viewing other records.
  6. When a specific page number is clicked, the data of that page needs to be loaded
  7. User can sort the results by First Name, Title, City, or Hire Date
  8. After sorting the results by a column, when the user clicks on a page number, the paging should happen based on the current sort order.

Here is the stored procedure which satisfies the above requirements. [code]

    1 CREATE PROCEDURE GetEmployees(

    2     @LastName VARCHAR(20) = NULL,

    3     @Title VARCHAR(20) = NULL,

    4     @City VARCHAR(20) = NULL,

    5     @PageSize INT = 5,

    6     @PageNumber INT = 1,

    7     @SortOrder VARCHAR(20) = 'LastName'

    8 )

    9 AS

   10 

   11 SET NOCOUNT ON

   12 /*

   13     Let us use a CTE to simplify the code. The below CTE makes the code easier

   14     to read and understand.

   15 */

   16 ;WITH emp AS (

   17 SELECT

   18     /*

   19         Based on the sort order passed into the stored procedure, a Record Identifier

   20         (sequential number) is generated using the ROW_NUMBER() method. The sequential

   21         number is generated in the sorted order.

   22     */

   23     CASE

   24         WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)

   25         WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)

   26         WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City)

   27         -- In all other cases, assume that @SortOrder = 'LastName'

   28         ELSE ROW_NUMBER()OVER (ORDER BY LastName)

   29     END AS RecID,

   30     LastName,

   31     FirstName,   

   32     Title,

   33     HireDate,

   34     City,

   35     Country,

   36     PostalCode

   37 FROM employees

   38 WHERE

   39     /*

   40         Apply the filter. If the filter is specified, then apply the filter.

   41         If not, ignore the filter.

   42     */

   43     (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')

   44     AND

   45     (@Title IS NULL OR Title LIKE '%' + @Title + '%')

   46     AND

   47     (@City IS NULL OR City LIKE '%' + @City + '%')

   48 )

   49 

   50 /*

   51     Select the final query result.

   52 */

   53 SELECT

   54     RecID,

   55     LastName,

   56     Title,

   57     HireDate,

   58     City

   59 FROM emp

   60 /*

   61     Apply a RANGE filter on the requested SORT ORDER to retrieve the records of the

   62     current page. If the "Page Number" is 3 and "Page Size" is 30 then records 61 to

   63     90 are retrieved.

   64 */

   65 WHERE RecID BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize

   66 /*

   67     "RecID" is a value generated by the previous CTE based on the sort order specified

   68     by the @SortOrder parameter.

   69 */

   70 ORDER BY RecID

Let us execute the stored procedure. [code]

    1 -- Let us retrieve the first page sorted by "Last Name"

    2 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 1, @SortOrder = 'LastName'

    3 

    4 /*

    5 OUTPUT:

    6 

    7 RecID                LastName             Title                         HireDate                City

    8 -------------------- -------------------- ------------------------------ ----------------------- ---------------

    9 1                    Buchanan             Sales Manager                 1993-10-17 00:00:00.000 London

   10 2                    Callahan             Inside Sales Coordinator       1994-03-05 00:00:00.000 Seattle

   11 3                    Davolio             Sales Representative           1992-05-01 00:00:00.000 Seattle

   12 */

   13 

   14 -- Let us retrieve the second page sorted by "Last Name"

   15 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 2, @SortOrder = 'LastName'

   16 

   17 /*

   18 OUTPUT:

   19 

   20 RecID                LastName             Title                         HireDate                City

   21 -------------------- -------------------- ------------------------------ ----------------------- ---------------

   22 4                    Dodsworth            Sales Representative           1994-11-15 00:00:00.000 London

   23 5                    Fuller               Vice President, Sales         1992-08-14 00:00:00.000 Tacoma

   24 6                    King                 Sales Representative           1994-01-02 00:00:00.000 London

   25 */

   26 

   27 -- Let us retrieve the third page sorted by "City"

   28 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 3, @SortOrder = 'City'

   29 

   30 /*

   31 OUTPUT:

   32 

   33 RecID                LastName             Title                         HireDate                City

   34 -------------------- -------------------- ------------------------------ ----------------------- ---------------

   35 7                    Davolio             Sales Representative           1992-05-01 00:00:00.000 Seattle

   36 8                    Callahan             Inside Sales Coordinator       1994-03-05 00:00:00.000 Seattle

   37 9                    Fuller               Vice President, Sales         1992-08-14 00:00:00.000 Tacoma

   38 */

Conclusions

There are different ways to implement the above functionality. The above code can be re-written in different ways. For example, the ORDER BY clause can take an expression which uses a CASE statement with ROW_NUMBER()

Total article views: 12829 | Views in the last 30 days: 21
 
Related Articles
FORUM

Order by numbers in proper sequence

Ordering by numbers in proper sequence order

FORUM

Report Builder Filter order the same as the actual model.

Report Builder Filter order the same as the actual model.

FORUM

Inserting Multiple Records Using Range Numbers

Inserting Multiple Records Using Range Numbers

FORUM

inconsistent record set using ROW_NUMBER() function

Getting inconsistent record set While doing paging using ROW_NUMBER() function.

FORUM

Distinct Top n Records with Order By

Distinct Top n Records with Order By

Tags
advanced querying    
t-sql    
 
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