Paginatoin with Separate Count Query

When implementing pagination in SQL Server, one common approach is to execute two separate queries—one to retrieve the total record count and another to fetch the actual data page.

-- Retrieve total number of matching records
SELECT COUNT(*) AS TotalRecords
FROM Products
WHERE CategoryId = 5 AND IsActive = 1;

-- Fetch specific page of results
SELECT ProductId, ProductName, Price, CreatedDate
FROM Products
WHERE CategoryId = 5 AND IsActive = 1
ORDER BY CreatedDate DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Single-Query Pagination Using Window Functions

SQL Server's window functions allow retrieving both data and total count in a single query, improving performance by reducing round trips to the database.

SELECT 
    ProductId,
    ProductName,
    Price,
    CreatedDate,
    COUNT(*) OVER () AS TotalRecords
FROM Products
WHERE CategoryId = 5 AND IsActive = 1
ORDER BY CreatedDate DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Practical Window Function Examples

ROW_NUMBER()

Assigns a unique sequential integer to each row, starting at 1 for the first row in the ordered partition.

SELECT 
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
    EmployeeName,
    Salary
FROM Staff;

RANK()

Assigns a ranking to each row within the partition. Identical values receive the same rank, and the next rank skips positions accordingly.

SELECT 
    RANK() OVER (ORDER BY Salary DESC) AS EmployeeRank,
    EmployeeName,
    Salary
FROM Staff;

DENSE_RANK()

Similar to RANK(), but without gaps in the ranking sequence. Identical values share the same rank, and the next rank follows consecutively.

SELECT 
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank,
    EmployeeName,
    Salary
FROM Staff;

NTILE()

Distributes rows into a specified number of buckets. Useful for creating quartiles, tertiles, or any equal-sized divisiosn.

SELECT 
    NTILE(4) OVER (ORDER BY Salary DESC) AS SalaryQuartile,
    EmployeeName,
    Salary
FROM Staff;

LAG()

Accesses data from a preceding row in the same result set without using a self-join. The optional second parameter specifies how many rows back to look.

SELECT 
    EmployeeName,
    Salary,
    LAG(Salary, 1) OVER (ORDER BY Salary DESC) AS PriorYearSalary
FROM Staff;

LEAD()

Accesses data from a subsequent row in the result set. Useful for comparing current values with next values.

SELECT 
    EmployeeName,
    Salary,
    LEAD(Salary, 1) OVER (ORDER BY Salary DESC) AS NextInRanking
FROM Staff;