SQL Server Interview Question #288

How should pagination be implemented efficiently?

ASP.NET Core, EF Core & SQL Server Real-World Scenarios Senior Advanced

Quick Interview Answer

For ordinary page-number interfaces, SQL Server commonly uses OFFSET/FETCH generated through EF Core Skip and Take. It works well for many workloads when supported by an appropriate deterministic index. For very deep pagination, OFFSET must still process or skip preceding rows and can become expensive. Keyset or seek pagination can be more scalable by requesting rows after the last seen key. Whichever strategy is used, ordering should be deterministic, often by adding a unique key as a tiebreaker.

Detailed Explanation

For ordinary page-number interfaces, SQL Server commonly uses OFFSET/FETCH generated through EF Core Skip and Take. It works well for many workloads when supported by an appropriate deterministic index.

For very deep pagination, OFFSET must still process or skip preceding rows and can become expensive. Keyset or seek pagination can be more scalable by requesting rows after the last seen key.

Whichever strategy is used, ordering should be deterministic, often by adding a unique key as a tiebreaker.

Code Example

var page = await db.Products
    .AsNoTracking()
    .OrderByDescending(p => p.CreatedAt)
    .ThenByDescending(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();