Server Side Paging With SQL Server 2005

,

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

Rate

4.86 (7)

Share

Share

Rate

4.86 (7)