ORDER BY, OFFSET & FETCH

Sorting and pagination are two of the most common requirements in any application that presents data to users. A product listing needs to show items by price. An order history needs the most recent orders first. A customer table in an admin panel needs to be browsable page by page without loading all ten thousand rows at once. SQL Server handles both with a single, cohesive syntax — ORDER BY controls the sort, and OFFSET with FETCH controls which slice of the sorted result is returned. Understanding how these work together, what makes pagination reliable versus fragile, and what the performance implications are at scale is essential for building queries that serve real applications correctly.

ORDER BY — Controlling Sort Order

ORDER BY is the only guaranteed way to control the sequence of rows returned by a query. Without it, SQL Server returns rows in whatever order the storage engine finds most convenient — typically clustered index order for a simple table scan, but this is an implementation detail that can change with query plan changes, index modifications, or data reorganisation. A query that has returned rows in a consistent order for months can silently change order after an index rebuild or a statistics update. ORDER BY is evaluated last in SQL Server's logical processing order, after all filtering, grouping, and projection — which means it can reference column aliases defined in SELECT and can use column position numbers, though position numbers are fragile and should be avoided in production code.

-- ORDER BY fundamentals — direction, multi-column, and expression-based sorting
USE DataplexaStore;

-- Single column descending — most expensive products first
SELECT ProductName, Category, Price
FROM Products
ORDER BY Price DESC;

-- Multi-column sort — category alphabetically, then price descending within each category
SELECT ProductName, Category, Price, StockQty
FROM Products
ORDER BY Category ASC, Price DESC;

-- Sort by a computed expression — sort by inventory value without selecting it
SELECT ProductName, Price, StockQty
FROM Products
ORDER BY Price * StockQty DESC;     -- computed expression in ORDER BY

-- Sort by alias defined in SELECT
SELECT
    ProductName,
    Price * StockQty    AS inventory_value
FROM Products
ORDER BY inventory_value DESC;      -- alias is valid in ORDER BY

• The inventory value sort produces a completely different ranking than the price sort — Monitor wins on unit price, but its low stock of 10 means Ergonomic Chair and Standing Desk follow; Ballpoint Pen Set with 200 units jumps high despite its low unit price
• A computed expression in ORDER BY is evaluated row by row — SQL Server does not require the expression to appear in the SELECT list, it computes it purely for sorting purposes
• Multi-column ORDER BY applies the second sort key only within groups where the first key produces ties — within Electronics all five rows have identical Category values, so Price DESC applies to all of them

NULL Sorting Behaviour

When a sorted column contains NULL values, SQL Server places NULLs at the end of an ascending sort and at the beginning of a descending sort. This is different from some other database systems — PostgreSQL and Oracle allow NULLS FIRST and NULLS LAST in ORDER BY, but T-SQL does not support this syntax. If you need NULLs in a specific position — for example always at the end regardless of sort direction — the standard T-SQL technique is to add a secondary sort key using a CASE expression that assigns a numeric priority to NULL values.

-- NULL sorting behaviour and controlling NULL position

-- Add a nullable column temporarily to demonstrate NULL sort position
ALTER TABLE Products ADD DiscountPct DECIMAL(5,2) NULL;

UPDATE Products SET DiscountPct = 10.00 WHERE ProductID IN (1, 3, 5);
UPDATE Products SET DiscountPct = 5.00  WHERE ProductID IN (2, 4);
-- ProductIDs 6-10 remain NULL

-- ASC sort — NULLs appear last
SELECT ProductName, DiscountPct
FROM Products
ORDER BY DiscountPct ASC;

-- DESC sort — NULLs appear first
SELECT ProductName, DiscountPct
FROM Products
ORDER BY DiscountPct DESC;

-- Force NULLs to the end regardless of sort direction
SELECT ProductName, DiscountPct
FROM Products
ORDER BY
    CASE WHEN DiscountPct IS NULL THEN 1 ELSE 0 END,  -- NULLs get priority 1 (sorts last)
    DiscountPct DESC;                                   -- non-NULLs sorted descending

-- Clean up the temporary column
ALTER TABLE Products DROP COLUMN DiscountPct;

• The CASE expression is evaluated before ORDER BY applies — it produces 0 for non-NULL rows and 1 for NULL rows, so the primary sort always puts non-NULLs first (0 sorts before 1), then the secondary sort orders the non-NULLs by DiscountPct DESC
• T-SQL's NULL sort position (last in ASC, first in DESC) is the opposite of PostgreSQL's default (first in ASC, last in DESC) — worth remembering if you move between database systems
• The CASE trick works for any sort direction and any data type — it is the portable T-SQL solution to the absence of NULLS FIRST / NULLS LAST syntax

OFFSET and FETCH — Reliable Pagination

OFFSET and FETCH are SQL Server's standard pagination syntax, available since SQL Server 2012. OFFSET skips a specified number of rows from the sorted result before returning anything. FETCH NEXT specifies how many rows to return after the skip. Together they define a window into the sorted result — skip the first 20 rows (page 1), return the next 10 (page 2), or skip 30 and return 10 (page 4). Both require ORDER BY to be present — OFFSET and FETCH are extensions of the ORDER BY clause and are syntactically attached to it. Without ORDER BY the result set has no defined sequence, so specifying a position within it is meaningless. Attempting to use OFFSET without ORDER BY raises an error.

-- OFFSET and FETCH — paginating through the Products table
-- Page size: 3 rows per page

-- Page 1 — skip 0 rows, fetch the first 3
SELECT ProductName, Category, Price
FROM Products
ORDER BY Price DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

-- Page 2 — skip 3 rows, fetch the next 3
SELECT ProductName, Category, Price
FROM Products
ORDER BY Price DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

-- Page 3 — skip 6 rows, fetch the next 3
SELECT ProductName, Category, Price
FROM Products
ORDER BY Price DESC
OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY;

-- Parameterised pagination — ready for use with application variables
DECLARE @PageNumber INT  = 2;    -- 1-based page number from the application
DECLARE @PageSize   INT  = 3;    -- rows per page

SELECT ProductName, Category, Price
FROM Products
ORDER BY Price DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS    -- convert 1-based page to 0-based offset
FETCH NEXT @PageSize ROWS ONLY;

• The formula (@PageNumber - 1) * @PageSize converts a 1-based page number (the way applications typically count pages) to a 0-based row offset (the way OFFSET counts) — page 1 skips 0 rows, page 2 skips 3, page 3 skips 6
• OFFSET and FETCH require a stable sort to produce consistent pages — if two products have identical prices and no tiebreaker column is included in ORDER BY, those two rows can appear on different pages on different executions
• Always add a unique column as a final ORDER BY tiebreaker (typically the primary key) when paginating — ORDER BY Price DESC, ProductID ASC guarantees every row has a unique position in the sort and pages never overlap

Pagination with a Total Row Count

Real pagination UIs need two things: the current page of rows and the total number of rows (to calculate how many pages exist and whether there is a next page). The naive approach is two separate queries — one for the page of data and one for COUNT(*). A more efficient approach uses COUNT(*) OVER() as a window function, which adds the total row count as an extra column on every row in the result, giving you both pieces of information in a single query. The window function is computed once and attached to each row — no second round trip to the database required.

-- Pagination with total count in a single query using COUNT(*) OVER()

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

SELECT
    ProductName,
    Category,
    Price,
    COUNT(*) OVER()                 AS total_rows,       -- total matching rows (no filter here)
    CEILING(COUNT(*) OVER() * 1.0
            / @PageSize)            AS total_pages       -- total pages at this page size
FROM Products
ORDER BY Price DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

• total_rows = 10 tells the application there are 10 products in total — it can display "Page 1 of 4" without a second query
• CEILING ensures partial pages are counted as a full page — 10 rows at 3 per page is 3.33 pages, which rounds up to 4; the fourth page will have only 1 row
• The 1.0 multiplier forces floating-point division — 10 / 3 would produce integer division (3), losing the remainder; 10 * 1.0 / 3 produces 3.33 which CEILING correctly rounds to 4

OFFSET Performance Considerations

OFFSET and FETCH are clean and standard, but they have a known performance characteristic at large offsets. To return page 1000 (skipping 999 pages of rows), SQL Server must still read and discard those 999 pages worth of rows before reaching the ones you want — there is no magic jump-to-position optimisation. For small to medium datasets and reasonable page depths this is fast enough to be unnoticeable. For very deep pagination — "show me page 5000 of 1-million-row result" — the seek-based pagination pattern is more efficient: instead of using a numeric offset, the last seen primary key value from the previous page is used as a WHERE filter to start the next page directly at the right position in the index.

-- Seek-based pagination — efficient alternative for deep pages
-- Instead of OFFSET, use the last seen value from the previous page as a WHERE filter

-- Simulate: application has just shown ProductID 7 (Ergonomic Chair) as the last row
-- Next page should start after ProductID 7, ordered by Price DESC then ProductID ASC

DECLARE @LastSeenPrice     DECIMAL(10,2) = 249.99;  -- price of last row on previous page
DECLARE @LastSeenProductID INT           = 7;        -- ProductID of last row on previous page
DECLARE @PageSize          INT           = 3;

SELECT TOP (@PageSize)
    ProductID,
    ProductName,
    Category,
    Price
FROM Products
WHERE Price < @LastSeenPrice                          -- rows after the last seen price
   OR (Price = @LastSeenPrice                         -- handle price ties using ProductID
       AND ProductID > @LastSeenProductID)
ORDER BY Price DESC, ProductID ASC;

• Seek-based pagination uses a WHERE clause that the index can satisfy directly — instead of skipping rows, SQL Server jumps straight to the first qualifying row after the last seen position, making deep pages as fast as page 1
• The tie-handling OR condition is required — without it, rows with the same Price as the last seen row would be incorrectly skipped; the ProductID tiebreaker ensures every row has a unique position
• The trade-off is that seek-based pagination does not support jumping to an arbitrary page number — you can only navigate forward sequentially, which is sufficient for infinite-scroll UIs but not for classic numbered pagination

Summary Table

Practice Questions

Quiz

Next up - Update Data - Modify existing rows in your tables with precision and learn how to avoid the most common UPDATE mistakes.