OFFSET and FETCH keywords

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

OFFSET/FETCH Pagination:- 
With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Stored procedure that was at least twice as fast in return times as the other average ones found on the internet.

Here I'm sharing 2 examples to achieving the SQL filtering, sorting and paging. Both example are tested and working fine but I will recommend example 1 for you.

Example 1,
--[dbo].[usp_GetemployeeCodes] NULL, 1, 10, 'employee_code', 'DESC'

CREATE PROCEDURE [dbo].[usp_GetEmployeeCodes]
(
    /* Optional Filters for Dynamic Search*/
    @SearchCode NVARCHAR(50) = NULL,

            /*– Pagination Parameters */
            @PageNo INT = 1,
            @PageSize INT = 15,

   /*– Sorting Parameters */
            @SortColumn NVARCHAR(20) = 'employee_code',
            @SortOrder NVARCHAR(4)='ASC'
)
AS
BEGIN
    /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE
            @lSearchCode NVARCHAR(50),

    @lPageNbr INT,
    @lPageSize INT,

    @lSortCol NVARCHAR(20),

    @lFirstRec INT,
    @lLastRec INT,
    @lTotalRows INT

    /*Setting Local Variables*/
            SET @lSearchCode =LTRIM(RTRIM(@SearchCode))

    SET @lPageNbr = @PageNo
    SET @lPageSize = @PageSize
    SET @lSortCol = LTRIM(RTRIM(@SortColumn))

    SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

   ; WITH CTE_Results
    AS (
    SELECT ROW_NUMBER() OVER (
                        ORDER BY
                                    CASE WHEN @lSortCol = 'employee_code' AND @SortOrder='ASC'
                                                  THEN employee_code                                                                                   
                                    END ASC,
                                    CASE WHEN @lSortCol = 'employee_code' AND @SortOrder='DESC'
                                                THEN employee_code                                   
                                    END DESC,

                                    CASE WHEN @lSortCol = 'employee_comp_code' AND @SortOrder='ASC'
                                                THEN employee_comp_code
                                    END ASC,
                                    CASE WHEN @lSortCol = 'employee_comp_code' AND @SortOrder='DESC'
                                                THEN employee_comp_code
                                    END DESC                   
  ) AS ROWNUM,
  Count(*) over () AS MaxRows,
                        LTRIM(RTRIM(employee_code)) AS employee_code
                        ,employee_comp_code         
 FROM [dbo].[employee_codes]
 WHERE           (@lSearchCode IS NULL OR employee_code LIKE '%' + @lSearchCode + '%')
)
SELECT
                        MaxRows,
                        LTRIM(RTRIM(employee_code)) AS employee_code
                        ,employee_comp_code                     
FROM CTE_Results AS CPC
    WHERE ROWNUM > @lFirstRec
     AND ROWNUM < @lLastRec
 ORDER BY ROWNUM ASC
END



Example 2,
--[dbo].[usp_GetEmployeeCodes] 'INC', 3, 15, 'employee_code', 'ASC' 
--[dbo].[usp_GetEmployeeCodes] '', 1, 15, 'employee_code', 'ASC' 
CREATE  PROC [dbo].[usp_GetEmployeeCodes]
(
            @SearchValue NVARCHAR(50) = NULL,

            @PageNo INT = 1,
            @PageSize INT = 10,

            @SortColumn NVARCHAR(20) = 'employee_code',
            @SortOrder NVARCHAR(20) = 'ASC'
)
 AS BEGIN
 SET NOCOUNT ON;
   SET @SearchValue = LTRIM(RTRIM(@SearchValue))
   ; WITH CTE_Results AS
   (
                                    SELECT
                 employee_code
                ,employee_comp_code               
            FROM [dbo].[employee_codes]
            WHERE (@SearchValue IS NULL OR employee_name LIKE '%' + @SearchValue + '%')
            ORDER BY
               CASE WHEN (@SortColumn = 'employee_code' AND @SortOrder='ASC')
                                                            THEN employee_code
               END ASC,

                                       CASE WHEN (@SortColumn = 'employee_code' AND @SortOrder='DESC')
                                                  THEN employee_code
                                       END DESC,

               CASE WHEN (@SortColumn = 'employee_comp_code' AND @SortOrder='ASC')
                    THEN employee_comp_code
               END ASC,

               CASE WHEN (@SortColumn = 'employee_comp_code' AND @SortOrder='DESC')
                   THEN employee_comp_code
               END DESC                  

                                    OFFSET @PageSize * (@PageNo - 1) ROWS
                        FETCH NEXT @PageSize ROWS ONLY
            ),
            CTE_TotalRows AS
            (
             SELECT count(employee_code) as MaxRows
                        FROM [dbo].[employee_codes]
                        WHERE (@SearchValue IS NULL OR employee_name LIKE '%' + @SearchValue + '%')
            )
                                     SELECT MaxRows
                        ,t.employee_code
                        ,t.employee_comp_code                  
             FROM [dbo].[employee_codes] as t, CTE_TotalRows
                         WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.employee_code = t.employee_code)
            OPTION (RECOMPILE)

 END

When to use Option (recompile)?
1.     Unpredictable amount of results returned
2.     Optimized query plan

Use WITH RECOMPILE or OPTION (RECOMPILE)


Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans.


OPTION (RECOMPILE) is used in the queries with parameters to prevent parameter sniffing issue.


Impact of option (recompile) when joins involved: -
The estimated rows won’t be accounted for the table join. Instead, the recompile will only provide back the count of the rows in the table variable, without accounting for the possible 1 to many. This is important to remember as option (recompile) added to a table variable with a 1-many won’t fix the correct statistics estimation issue.


Disadvantages of Option (recompile) -
1.     Lose DM Exec query stats.
2.     Can have tremendous impact on CPU


I hope it will helps you a lots. If you have another one method to achieve this. Please send me. I will update here as an example 3. Thank you.

ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
My Blogs - https://code-sample.com and https://code-sample.xyz
My Book1 - BEST SELLING ANGULAR BOOK (INCLUDING ALL VERSIONS 2, 4, 5, 6, 7)
My Book2 - ANGULAR 2 INTERVIEW QUESTIONS BOOK - Both Books are Available on WorldWide.

www.code-sample.com/. Powered by Blogger.
^