In SQL Server, how do TOP (n) and OFFSET ... FETCH differ, and what does each require of ORDER BY?
answer
- one is T-SQL's own, one is standard
- ORDER BY optional for only one
- OFFSET is part of ORDER BY
- WITH TIES belongs to TOP alone
- never both in one query scope
basics
~20 sTOP (n) is T-SQL's own limiter: ORDER BY is optional, but without it the rows chosen are undefined, and only TOP offers WITH TIES and PERCENT. OFFSET ... FETCH is part of ORDER BY, so it needs one, and it is the paging tool.
solid answer
~40 s`TOP (n)` sits in the select list and also works in `INSERT`, `UPDATE`, `DELETE` and `MERGE`, where the parentheses are required. Without `ORDER BY` it returns *some* n rows in an undefined order, so in a `SELECT` you always pair it with one. Only `TOP` has `PERCENT` and `WITH TIES`, and `WITH TIES` is legal only in a `SELECT` that has an `ORDER BY`. `OFFSET ... FETCH` is an extension of the `ORDER BY` clause itself: it cannot exist without `ORDER BY`, `OFFSET` is mandatory even as `OFFSET 0 ROWS`, and SQL Server's `FETCH` ends in `ONLY`, with no `WITH TIES`. You cannot mix `TOP` and `OFFSET ... FETCH` in one query scope, and SQL Server has no `LIMIT` keyword at all.
code
sql · 10 lines-- Ten most recent orders, plus any that tie with the tenth on order_date
SELECT TOP (10) WITH TIES order_id, customer_id, order_date
FROM dbo.orders
ORDER BY order_date DESC;
-- Page 3 at 20 rows a page; order_id breaks ties so pages are stable
SELECT order_id, customer_id, order_date
FROM dbo.orders
ORDER BY order_date DESC, order_id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;go deeper
Recall that SQL Server has no LIMIT, that TOP needs ORDER BY to be predictable, and that OFFSET ... FETCH only exists as the tail of an ORDER BY clause.
Explain the grammar behind the rules: OFFSET is mandatory with FETCH, only TOP supports WITH TIES and PERCENT, and TOP in UPDATE or DELETE takes no ORDER BY.
Show you catch the quiet bugs: TOP without ORDER BY passing tests on small tables, unordered DELETE TOP purges, and non-unique paging keys that repeat rows.
Speak to porting cost: a codebase full of LIMIT needs mechanical rewrites, and a team rule that every SELECT TOP carries an ORDER BY prevents nondeterministic results.
## Two row limiters with different origins SQL Server gives you two ways to cut a result down to a number of rows. **`TOP`** is T-SQL's own clause; it is written straight after `SELECT` (or after the verb of a data modification statement). **`OFFSET ... FETCH`** is the ANSI-style form, and in SQL Server it is not a separate clause at all but an optional tail of the **`ORDER BY`** clause. That single grammatical fact explains most of the rules below. There is no `LIMIT` keyword in SQL Server. A query ported from an engine that uses `LIMIT 10 OFFSET 20` fails to parse and must be rewritten with one of these two forms. ## TOP (n): what T-SQL lets you do - **Syntax**: `SELECT TOP (10) ...`. The parentheses are optional in a `SELECT` only when the value is an integer constant, kept for backward compatibility; they are required in `INSERT`, `UPDATE`, `DELETE` and `MERGE`, so the documentation recommends always writing them. - **Expression**: the value can be a variable or expression, converted to `bigint`, or to `float` when you add `PERCENT`. - **`PERCENT`**: `TOP (5) PERCENT` returns that share of the rows, with a fractional row count rounded up. - **`WITH TIES`**: adds every row that ties with the last row on the `ORDER BY` columns, so `TOP (5) WITH TIES` can return seven rows. It is allowed only in a `SELECT` and only with an `ORDER BY`. - **No `ORDER BY`**: legal, but the rows returned are whichever the plan produces first. Nothing guarantees clustered-key order, insertion order or the same rows on the next run. In data modification statements `TOP` takes no `ORDER BY` at all, so `DELETE TOP (1000) FROM dbo.audit_log` removes 1,000 arbitrary rows. To delete the oldest rows, choose their keys in a subquery that can be ordered. ## OFFSET ... FETCH: paging inside ORDER BY The grammar is `ORDER BY ... OFFSET n ROWS [FETCH { FIRST | NEXT } m ROWS ONLY]`. The consequences: 1. **`ORDER BY` is mandatory**, because `OFFSET` is part of it. 2. **`OFFSET` is mandatory** even when you skip nothing: to take the first 20 rows you write `OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY`. `FETCH` on its own is a syntax error. 3. **No `WITH TIES`**: SQL Server's `FETCH` accepts only `ONLY`. If you need ties, use `TOP ... WITH TIES`. 4. `FIRST` and `NEXT`, and `ROW` and `ROWS`, are synonyms provided for ANSI compatibility. 5. The counts can be variables, parameters or a constant scalar subquery that does not reference the outer query. `OFFSET ... FETCH` cannot be written directly in `INSERT`, `UPDATE`, `DELETE` or `MERGE`, only in a subquery inside them, and the `OVER` clause does not support it. In a query with `UNION`, `EXCEPT` or `INTERSECT`, only the final query that orders the result may carry it. ## Side by side | | `TOP (n)` | `OFFSET ... FETCH` | |---|---|---| | Where it is written | after `SELECT` or the DML verb | at the end of `ORDER BY` | | Needs `ORDER BY` | no, but rows are undefined without it | yes, always | | Skips rows | no | yes, `OFFSET n ROWS` | | `WITH TIES` / `PERCENT` | yes / yes | no / no | | Directly in `UPDATE` / `DELETE` | yes | no, only in a subquery | The two cannot be combined in the same query scope. In an execution plan both show up as a **Top** operator, with the offset in its `Offset` attribute. ## Mistakes when porting LIMIT queries - Writing `TOP (20) ... OFFSET 40 ROWS` to get page three: rejected, because `TOP` and `OFFSET` cannot share a scope. - Putting `TOP (1)` in each branch of a `UNION ALL` and one `ORDER BY` at the end: the final `ORDER BY` sorts the union, while each `TOP` picks an arbitrary row. Put each `TOP` and its own `ORDER BY` in a derived table. - Assuming `TOP` without `ORDER BY` follows the clustered index. It often does on a small table, which is why the bug survives testing. - Paging on a non-unique sort key: add a unique column such as the primary key as the last `ORDER BY` column, or rows can repeat or vanish between pages.
- How do you delete the 1,000 oldest rows with TOP when DELETE does not accept ORDER BY?Choose the keys in a subquery, where `ORDER BY` is allowed: `DELETE FROM dbo.audit_log WHERE log_id IN (SELECT TOP (1000) log_id FROM dbo.audit_log ORDER BY logged_at);`. Select a unique key; selecting a non-key column with duplicates can delete more rows than intended. A bare `DELETE TOP (1000)` removes arbitrary rows, which is fine for a chunked purge where order does not matter.
- Why does SELECT TOP (1) ... UNION ALL SELECT TOP (1) ... ORDER BY price not return the cheapest row of each branch?The final `ORDER BY` applies to the whole union, and each branch's `TOP` is applied without it, so each branch returns an arbitrary row and only the combined two rows get sorted. Wrap each branch in a derived table with its own `TOP (1)` and `ORDER BY price`, then union the derived tables.
- SQL Server accepts LIMIT 10 OFFSET 20 at the end of a SELECT.
- TOP without ORDER BY returns rows in clustered index order.
- OFFSET ... FETCH works without an ORDER BY clause.
- FETCH NEXT 10 ROWS WITH TIES works in SQL Server like TOP WITH TIES.
- TOP and OFFSET can be combined in one SELECT to skip and then limit.
- DELETE TOP (100) removes the 100 oldest rows.