I encountered some performance issues using the COUNT() OVER() method. (I’m not sure if it was the server as it took 40 seconds to return 10 records and then later didn’t have any issues.) This technique worked under all conditions without having to use COUNT() OVER() and accomplishes the same thing:
DECLARE
@PageSize INT = 10,
@PageNum INT = 1;
WITH TempResult AS(
SELECT ID, Name
FROM Table
), TempCount AS (
SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult, TempCount
ORDER BY TempResult.Name
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY