Even though there are dozens of articles out there which shows you how to do a Microsoft SQL Server stored procedure with filtering, sorting and paging, the execution time greatly differs between each approach.
So I started to do some research in order to find the best approach out there. And I believe I found it on Aaron Bertrand article about 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.
You can check it in action out below exemplified on a simple Languages table:
ALTER PROCEDURE [dbo].[ListLanguages]
@SearchValue NVARCHAR(50) = NULL,
@PageNo INT = 1,
@PageSize INT = 10,
@SortColumn NVARCHAR(20) = 'Name',
@SortOrder NVARCHAR(20) = 'ASC'
SET NOCOUNT ON;
SET @SearchValue = LTRIM(RTRIM(@SearchValue))
; WITH CTE_Results AS
SELECT LanguageId, Name, Code from Language
WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')
CASE WHEN (@SortColumn = 'Code' AND @SortOrder='ASC')
CASE WHEN (@SortColumn = 'Code' AND @SortOrder='DESC')
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
select count(LanguageID) as MaxRows from Language WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')
Select MaxRows, t.LanguageId, t.Name, t.Code from dbo.Language as t, CTE_TotalRows
WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.LanguageID = t.LanguageID)
Try this approach on your side, especially if you have a different implementation approach. I am super curious to see if it made any difference in terms of performance. On my side for a SQL clustered table with 100+ k rows it got me 2.3x improvement in execution times.
You can download it from below: