which is the optimized way for server-side pagination in SQL using DENSE_RANK() or OFFSET? OFFSET Query: SELECT * FROM Tbl_Employee OFFSET @PageSize * (@Page - 1) ROWS FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); DENSE_RANK(): SELECT * FROM ( SELECT DENSE_RANK() OVER ( ORDER BY FullName ASC ) AS RowNo,* FROM Tbl_Employee ) TAB WHERE RowNo >= 1 and RowNo ORDER BY 2 ASC ;

OFFSET is use for Pagination where as DENSE_RANK(),ROW_NUMBER and NITILE are used for Ranking on column values CREATE PROCEDURE dbo.Pagination_Test_1 -- ORDER BY CustomerID @PageNumber INT = 1, @PageSize INT = 100 AS BEGIN SET NOCOUNT ON ; SELECT CustomerID, FirstName, LastName, EMail, Active, Created, Updated FROM dbo.Customers_I ORDER BY CustomerID OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); END GO DENSE_RANK() SELECT i.ProductID, p. Name , i.LocationID, i.Quantity ,DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC ) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID; GO