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.
When to use Option (recompile)?
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) -
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.