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

LIKE, IN, BETWEEN: Advanced Filtering Patterns in SQL Server

Basic equality filters only get you so far. Here’s the rest of the filtering toolkit — wildcard patterns, membership tests, ranges, and combining them all with AND/OR/NOT correctly — plus a NULL-comparison bug that costs beginners hours of confused debugging because it never throws an error.

Comparison and Logical Operators

SELECT name FROM dbo.Restaurant WHERE rating >= 4.5;
SELECT name FROM dbo.Restaurant WHERE city = 'Austin' AND rating > 4.0;
SELECT name FROM dbo.Restaurant WHERE cuisine = 'Indian' OR cuisine = 'Japanese';
SELECT name FROM dbo.Restaurant WHERE NOT (city = 'Dallas');

Operator Precedence: A Real Beginner Trap

-- Looks like "Austin restaurants, plus anything highly rated" — but AND binds tighter than OR:
SELECT name FROM dbo.Restaurant
WHERE city = 'Austin' OR cuisine = 'Italian' AND rating > 4.5;
-- Actually means: city = 'Austin' OR (cuisine = 'Italian' AND rating > 4.5)

-- Use explicit parentheses to say what you actually mean:
SELECT name FROM dbo.Restaurant
WHERE (city = 'Austin' OR cuisine = 'Italian') AND rating > 4.5;

IN: A Cleaner Way to Write Multiple ORs

SELECT name FROM dbo.Restaurant
WHERE cuisine IN ('Indian', 'Japanese', 'Italian');

-- NOT IN excludes a list — but see the NULL warning below before relying on this
SELECT name FROM dbo.Restaurant
WHERE cuisine NOT IN ('Mexican');

BETWEEN: Inclusive on Both Ends

SELECT name, rating FROM dbo.Restaurant
WHERE rating BETWEEN 4.0 AND 4.5;
-- Equivalent to: rating >= 4.0 AND rating <= 4.5 — both endpoints are included

LIKE: Pattern Matching

SELECT name FROM dbo.Restaurant WHERE name LIKE 'T%';        -- starts with T
SELECT name FROM dbo.Restaurant WHERE name LIKE '%Grill%';    -- contains "Grill" anywhere
SELECT name FROM dbo.Restaurant WHERE name LIKE '_a%';        -- 2nd letter is 'a'
SELECT name FROM dbo.Restaurant WHERE name LIKE '[ST]%';      -- starts with S or T

The NULL Trap

NULL means "unknown," and SQL uses three-valued logic — unknown = unknown evaluates to unknown, not true. WHERE column = NULL silently returns zero rows every single time, with no error to tip you off. Always use IS NULL / IS NOT NULL instead.

NULL's Sequel: The NOT IN Trap

-- If ANY value in the list (or subquery result) is NULL, the ENTIRE NOT IN silently
-- matches zero rows — not an error, just wrong results:
SELECT name FROM dbo.Restaurant
WHERE cuisine NOT IN (SELECT cuisine FROM dbo.Restaurant WHERE cuisine IS NULL OR city = 'Dallas');
-- If that subquery returns even one NULL alongside real values, this returns nothing at all.

-- Safer: explicitly exclude NULLs from the list first, or use NOT EXISTS instead (Chapter 5)
SELECT name FROM dbo.Restaurant r
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.Restaurant r2 WHERE r2.city = 'Dallas' AND r2.cuisine = r.cuisine
);