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 Storedprocedure that was at least twice as fast in return times as the other average ones found on the internet.

As an Example – 
--[dbo].[usp_GetEmployeeCodes] 'INC', 3, 15, 'employee_code', 'ASC'  
--[dbo].[usp_GetEmployeeCodes] '', 1, 15, 'employee_code', 'ASC'  
ALTER 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     
                        ,employee_group_desc
                        ,employee_name  
                        ,employee_addr1 
                        ,employee_addr2 
                        ,employee_city
                        ,employee_state  
                        ,employee_zip  
                        ,Country
                        ,employee_phone
            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,


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                    CASE WHEN (@SortColumn = 'employee_phone' AND @SortOrder='DESC')
                                                                        THEN employee_phone
                                    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     
                        ,t.employee_group_desc
                        ,t.employee_name  
                        ,t.employee_addr1 
                        ,t.employee_addr2 
                        ,t.employee_city
                        ,t.employee_state  
                        ,t.employee_zip  
                        ,t.Country
                        ,t.employee_phone
   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
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.
^