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' ) AS BEGIN 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 + '%') ORDER BY CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC') THEN Name END ASC, CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC') THEN Name END DESC, CASE WHEN (@SortColumn = 'Code' AND @SortOrder='ASC') THEN Code END ASC, CASE WHEN (@SortColumn = 'Code' AND @SortOrder='DESC') THEN Code END DESC OFFSET @PageSize * (@PageNo - 1) ROWS FETCH NEXT @PageSize ROWS ONLY ), CTE_TotalRows AS ( 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) OPTION (RECOMPILE) END GO
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: