As a web developer, you may have encountered the need to implement server side paging of data to improve the performance of your application. Without server side paging, the application fetches all the data from the database server and loads a specific number of records based on the current page being viewed by the user. This means that a large amount of unnecessary data is being fetched.

To address this issue, there are various approaches available. However, many of the existing articles on the internet do not consider all the requirements that you may be looking for. In this article, we will discuss a comprehensive approach to implementing server side paging in SQL Server.

One important consideration is selecting the required number of records based on the current page count and page size. For example, if the page size is set to 25 records and the user is on page 4, then only records 76 to 100 should be retrieved from the database. Additionally, the code should handle sorting and filtering requirements. The data to be retrieved for a specific page may vary depending on the sort order and any applied filters.

Let’s take a look at a sample stored procedure that fulfills these requirements:

CREATE PROCEDURE GetEmployees
(
    @LastName VARCHAR(20) = NULL,
    @Title VARCHAR(20) = NULL,
    @City VARCHAR(20) = NULL,
    @PageSize INT = 10,
    @PageNumber INT = 1,
    @SortOrder VARCHAR(20) = 'LastName'
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH emp AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY
                CASE
                    WHEN @SortOrder = 'Title' THEN Title
                    WHEN @SortOrder = 'HireDate' THEN HireDate
                    WHEN @SortOrder = 'City' THEN City
                    ELSE LastName
                END) AS RecID,
            LastName,
            FirstName,
            Title,
            HireDate,
            City,
            Country,
            PostalCode
        FROM employees
        WHERE
            (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
            AND (@Title IS NULL OR Title LIKE '%' + @Title + '%')
            AND (@City IS NULL OR City LIKE '%' + @City + '%')
    )

    SELECT
        RecID,
        LastName,
        Title,
        HireDate,
        City
    FROM emp
    WHERE RecID BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
    ORDER BY RecID;
END;

In this example, we are using the NorthWind database and creating a stored procedure to retrieve a list of employees. The stored procedure accepts parameters for filtering, sorting, page size, and page number. It uses a common table expression (CTE) to simplify the code and generate a sequential record identifier based on the specified sort order. The final query selects the records for the current page based on the record identifier and applies the sort order.

Let’s execute the stored procedure to see the results:

-- Retrieve the first page sorted by "Last Name"
EXECUTE GetEmployees @PageSize = 3, @PageNumber = 1, @SortOrder = 'LastName'

-- Retrieve the second page sorted by "Last Name"
EXECUTE GetEmployees @PageSize = 3, @PageNumber = 2, @SortOrder = 'LastName'

-- Retrieve the third page sorted by "City"
EXECUTE GetEmployees @PageSize = 3, @PageNumber = 3, @SortOrder = 'City'

By implementing server side paging in SQL Server, you can significantly improve the performance of your application. The code provided in this article offers a comprehensive solution that considers sorting, filtering, and efficient data retrieval. Feel free to modify and adapt the code to suit your specific requirements.

Remember, there are different ways to implement this functionality, and the code can be customized based on your needs. Experiment with different approaches and optimize the code to achieve the best performance for your application.