Using SQL's OFFSET and FETCH with Window Functions for Efficient Pagination and Total Count

In modern applications, efficient data retrieval is crucial for a seamless user experience. One common scenario is paginating results, where a subset of records is displayed, and users can navigate to subsequent pages to view more. Additionally, it's often useful to show the total number of records, giving users a sense of the dataset's size.

SQL provides powerful tools to achieve this, and in this article, we'll explore how to use the OFFSET and FETCH clauses in conjunction with window functions to paginate results and obtain the total count in a single query.

The Problem

Consider a scenario where you're building a dashboard for a company's resources. You want to display a list of resources, the number of projects associated with each resource, and the total number of resources. The catch? You only want to show a limited number of resources per page, but you still want to display the total count of resources.

A naive approach might involve two separate queries: one to fetch the paginated results and another to get the total count. This is inefficient and can strain the database, especially with large datasets.

The Solution

The solution lies in SQL's window functions and the OFFSET and FETCH clauses. Here's a breakdown of the provided SQL code:

1. Window Function for Total Count: The line NbTotalResources = COUNT(*) OVER() uses the COUNT window function to get the total number of resources. The OVER() clause ensures that the count is calculated over the entire result set, not just the paginated subset.

2. Ordering and Pagination: The ORDER BY clause sorts resources by their keys. The OFFSET and FETCH clauses handle pagination. OFFSET skips a specified number of rows, and FETCH limits the number of rows returned.

Code Example:

SELECT  dbo.Resources.ID as ResourceID,
        dbo.Resources.CompanyID as CompanyID,
        dbo.Resources.ResourceKey as ResourceKey,
        count(dbo.ProjectResources.ID) as NbProjects,
        NbTotalResources = COUNT(*) OVER() 

FROM dbo.Resources (NOLOCK)
    LEFT JOIN dbo.ProjectResources (NOLOCK) ON (dbo.ProjectResources.ResourceID = dbo.Resources.ID )
WHERE dbo.Resources.CompanyID = @companyid
 AND (@searchQuery='' OR dbo.Resources.ResourceKey like '%' + @searchQuery + '%')
GROUP BY dbo.Resources.ID,
        dbo.Resources.CompanyID,
        dbo.Resources.ResourceKey
ORDER BY dbo.Resources.ResourceKey
OFFSET @OffsetX ROWS 
FETCH NEXT @MaxRows ROWS ONLY;

Conclusion

By leveraging SQL's powerful features, developers can efficiently paginate results and obtain aggregate data in a single query. This not only reduces the number of database calls but also ensures that applications remain performant and responsive.