Home / Intermediate SQL / OFFSET-FETCH / T-SQL / TOP, OFFSET-FETCH, and Pagination Queries Done Right in SQL Server

TOP, OFFSET-FETCH, and Pagination Queries Done Right in SQL Server

On

If you've ever built a web application or a report that displays thousands of customer orders, you already know the problem — returning all rows at once is slow, expensive, and terrible for the user experience. This is where SQL Server pagination with OFFSET-FETCH becomes one of the most practical skills in your T-SQL toolkit.

In this post, you'll learn how to use the TOP clause and the OFFSET-FETCH syntax to control how many rows SQL Server returns, when to use each approach, and how to build clean, production-ready pagination queries using the WideWorldImporters sample database. Whether you're powering a data grid in a web app or limiting rows in a report, this guide has you covered.

Understanding the TOP Clause

The TOP clause is the oldest and simplest way to limit rows in SQL Server. It tells the query engine to return only the first N rows from the result set.

Basic TOP Syntax

SELECT TOP (10)
    CustomerID,
    CustomerName,
    CreditLimit
FROM Sales.Customers
ORDER BY CustomerName ASC;

This returns the first 10 customers alphabetically from the Sales.Customers table. Clean and simple.

TOP with a Percentage

You can also retrieve a percentage of rows instead of a fixed number:

SELECT TOP (5) PERCENT
    OrderID,
    CustomerID,
    OrderDate,
    ExpectedDeliveryDate
FROM Sales.Orders
ORDER BY OrderDate DESC;

This returns the top 5% of orders by most recent date — useful for quick sampling or reporting previews.

TOP with WITH TIES

When you use ORDER BY and there are rows with identical values at the boundary, WITH TIES ensures SQL Server includes all of them rather than cutting off arbitrarily:

SELECT TOP (5) WITH TIES
    si.StockItemID,
    si.StockItemName,
    si.UnitPrice
FROM Warehouse.StockItems si
ORDER BY si.UnitPrice DESC;

If multiple stock items share the same price at position 5, they all get included in the results. This is a small but important detail that can prevent data integrity surprises in reporting.

When TOP Falls Short

Here's the developer reality: TOP is great for "give me the most recent 10 orders," but it falls apart the moment someone asks "give me orders 21 through 30." For that kind of page-by-page navigation, you need OFFSET-FETCH.

OFFSET-FETCH: The Right Way to Paginate in SQL Server

Introduced in SQL Server 2012, OFFSET-FETCH is the ANSI-standard approach to pagination. It lets you skip a defined number of rows (OFFSET) and then retrieve the next batch (FETCH NEXT).

Basic OFFSET-FETCH Syntax

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    ExpectedDeliveryDate
FROM Sales.Orders
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

This retrieves page 1 (rows 1–10). To get page 2 (rows 11–20):

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    ExpectedDeliveryDate
FROM Sales.Orders
ORDER BY OrderDate DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

The pattern is straightforward: OFFSET = (PageNumber - 1) * PageSize.

Building a Reusable Pagination Query

In real applications, page number and page size come from the UI or a stored procedure parameter. Here's how you'd write that cleanly using variables:

DECLARE @PageNumber INT = 3;
DECLARE @PageSize   INT = 10;

SELECT
    c.CustomerID,
    c.CustomerName,
    c.CreditLimit,
    c.PhoneNumber
FROM Sales.Customers c
ORDER BY c.CustomerName ASC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

This returns page 3 of customers in alphabetical order, 10 customers per page. Swap in any page number and page size and this query handles it cleanly.

Joining Tables in a Paginated Query

Real-world pagination usually involves joins. Here's a practical example pulling order data with customer names and city information:

DECLARE @PageNumber INT = 2;
DECLARE @PageSize   INT = 15;

SELECT
    o.OrderID,
    c.CustomerName,
    ct.CityName,
    o.OrderDate,
    o.ExpectedDeliveryDate,
    o.IsUndersupplyBackordered
FROM Sales.Orders o
    INNER JOIN Sales.Customers c  ON o.CustomerID = c.CustomerID
    INNER JOIN Application.Cities ct ON c.DeliveryCityID = ct.CityID
ORDER BY o.OrderDate DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

This gives you a clean, paginated order list showing customer name, city, and order details — exactly the kind of query you'd see powering a data grid in an order management application.

Getting the Total Row Count for Pagination UI

One thing developers often miss early on: the UI needs to know the total number of rows to calculate how many pages exist. You have two options.

Option 1 — Separate COUNT Query

SELECT COUNT(*) AS TotalOrders
FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;

Run this alongside your paginated query to tell the front end "there are 73,595 orders total — here's page 2 of 4,907."

Option 2 — COUNT OVER() in One Query

DECLARE @PageNumber INT = 1;
DECLARE @PageSize   INT = 10;

SELECT
    o.OrderID,
    c.CustomerName,
    o.OrderDate,
    COUNT(*) OVER() AS TotalRows
FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
ORDER BY o.OrderDate DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

The COUNT(*) OVER() window function returns the total row count in every row of the result without a second round trip to the database. This is the approach many experienced developers prefer for keeping things in a single query.

Paginating Stock and Supplier Data

Here's another realistic scenario — an inventory management screen browsing through stock items with supplier information:

DECLARE @PageNumber INT = 1;
DECLARE @PageSize   INT = 20;

SELECT
    si.StockItemID,
    si.StockItemName,
    si.UnitPrice,
    si.QuantityPerOuter,
    sp.SupplierName,
    sih.QuantityOnHand
FROM Warehouse.StockItems si
    INNER JOIN Purchasing.Suppliers sp       ON si.SupplierID = sp.SupplierID
    INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
ORDER BY si.StockItemName ASC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

This gives inventory managers a paginated view of stock items with live quantity-on-hand data and supplier names — a genuinely common reporting requirement.

TOP vs. OFFSET-FETCH: When to Use Which

Both tools have their place, and knowing when to reach for each one separates good SQL developers from great ones.

Use TOP when:

  • You want the single most recent record, the highest-value customer, or a quick sample
  • You're writing a quick lookup or diagnostic query
  • You don't need to navigate through pages

Use OFFSET-FETCH when:

  • You're building any kind of paginated list or data grid
  • Users need to navigate forward and backward through pages
  • You need consistent, predictable row-skipping behavior

One important rule: OFFSET-FETCH requires an ORDER BY clause. Without it, SQL Server throws an error. This is actually a good thing — pagination without a deterministic sort order produces unpredictable, unreliable results anyway.

Common Mistakes to Avoid

Forgetting ORDER BY with OFFSET-FETCH. SQL Server will not run the query without it. Always sort by a unique or meaningful column — OrderID, CustomerID, or a timestamp — to ensure consistent page results.

Using TOP for pagination. You'll see older code that uses TOP with subqueries to simulate pagination. It works, but it's messy, harder to maintain, and slower than OFFSET-FETCH. If you're on SQL Server 2012 or later, there's no reason to do this.

Not indexing your ORDER BY column. If you're paginating through Sales.Orders sorted by OrderDate, make sure OrderDate is indexed. Without it, SQL Server scans the entire table to sort before skipping — painful at scale.

Off-by-one errors in OFFSET calculation. The formula (PageNumber - 1) * PageSize is your friend. Page 1 → OFFSET 0. Page 2 → OFFSET 10 (for page size 10). Double-check your math before shipping.

Ignoring the total count. A pagination UI without a total row count is frustrating for users. Always include a total count — either from a second query or a window function.

Summary / Key Takeaways

  • TOP is ideal for simple row-limiting: grab the newest, biggest, or first N rows quickly.
  • OFFSET-FETCH is the correct, ANSI-standard tool for pagination — skip rows, fetch the next batch, repeat.
  • Always pair OFFSET-FETCH with a meaningful ORDER BY to guarantee consistent results across pages.
  • Use COUNT(*) OVER() to return the total row count in a single paginated query.
  • Index the columns you sort by to keep paginated queries fast on large datasets.
  • Use parameterized @PageNumber and @PageSize variables to make your pagination queries flexible and reusable.

FAQ

Q: Can I use OFFSET-FETCH in SQL Server 2008? No. OFFSET-FETCH was introduced in SQL Server 2012. On older versions, developers used ROW_NUMBER() in a CTE or subquery to simulate pagination. If you're still on 2008, it's a strong sign it's time to upgrade.

Q: Does OFFSET-FETCH work in SQL Server views? Yes, but with a catch — a view with ORDER BY requires TOP or OFFSET-FETCH to be included. In practice, most developers handle pagination at the stored procedure or application query layer rather than inside views, which keeps views more reusable.

Q: What happens if I request a page beyond the last row? SQL Server simply returns an empty result set — no error. Your application should handle this gracefully, typically by checking if the returned row count is zero and stopping navigation.

Tags: