SQL Server Pagination: OFFSET-FETCH and Multi-Column Sorting Explained

Real apps rarely show every row at once. Here’s how to sort by more than one column, split results into pages the correct way, and why a well-meaning shortcut (paginating without ORDER BY) causes bugs that only show up in production under load.

Multi-Column Sort

SELECT name, city, rating FROM dbo.Restaurant
ORDER BY city ASC, rating DESC;

City sorts alphabetically first; within each city, restaurants sort by rating, highest first. Each column can independently be ASC (default) or DESC — read it left to right as “sort by this, and within ties, sort by this next.”

-- Sorting by an expression, not just a raw column, works too:
SELECT name, rating, price_range FROM dbo.Restaurant
ORDER BY rating / price_range DESC; -- crude "value for money" ranking

You can also sort by column position (ORDER BY 3 DESC) — it works, but avoid it in real code; a column reordering elsewhere in the query silently changes what you sort by, with no warning.

OFFSET-FETCH: The Standard Way to Paginate

SELECT name, rating FROM dbo.Restaurant
ORDER BY rating DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;    -- page 1 (rows 1-3)

SELECT name, rating FROM dbo.Restaurant
ORDER BY rating DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;    -- page 2 (rows 4-6)

The general formula for “page N with a page size of S” is OFFSET (N-1) * S ROWS FETCH NEXT S ROWS ONLY — this is exactly the calculation a web application’s backend does every time you click “next page” on a paginated table.

Why ORDER BY Is Non-Negotiable Here

Without a defined sort order, SQL Server makes no guarantee about row order — “skip 3, take 3” is meaningless if the underlying order can silently shift between calls. OFFSET-FETCH is actually a syntax extension of ORDER BY itself in T-SQL — you cannot write it without an ORDER BY clause at all; SQL Server will raise a syntax error, which is the engine protecting you from this exact bug.

A Subtler Pagination Bug: Ties

-- Two restaurants both rated 4.7 could land on either side of a page boundary
-- unpredictably, if rating is the ONLY sort column and duplicates exist.
SELECT name, rating FROM dbo.Restaurant
ORDER BY rating DESC
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

-- Fix: add a tie-breaker column that's guaranteed unique, like the primary key
SELECT name, rating FROM dbo.Restaurant
ORDER BY rating DESC, restaurant_id ASC
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

Key Takeaways

• List multiple ORDER BY columns to sort within a sort — first column is primary, rest break ties
• OFFSET-FETCH is the standard SQL Server pagination pattern: skip N rows, take the next M — and T-SQL enforces that it can’t be used without ORDER BY
• Add a unique tie-breaker column (usually the primary key) to ORDER BY whenever the sort column can have duplicate values, to keep pagination stable