Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 39 ยท T-SQL Querying

Sorting and Paging with ORDER BY, OFFSET and FETCH

Sort SQL Server results with ORDER BY, handle NULL ordering and collations, and page through large result sets with OFFSET and FETCH, including keyset paging.

A table has no inherent order. Unless you say otherwise, SQL Server may return rows in whatever order is cheapest at that moment, and that order can change after an index is added or the data grows. If order matters to you or your users, ask for it with ORDER BY. This lesson covers sorting, how NULL and text collations affect the result, and the two ways to page through big result sets: OFFSET ... FETCH and the faster keyset approach.

ORDER BY basics

ORDER BY goes last in the query. List columns in priority order and add ASC (the default) or DESC to each:

SQL Sort by two columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You can also sort by an expression, by a SELECT alias, or by the position of a column in the select list (ORDER BY 2). Avoid positions in real code: reordering the select list silently changes the sort. You may sort by columns you do not select, except when you use DISTINCT or UNION, where ORDER BY can use only columns in the result.

ORDER BY is not allowed in views, inline functions, derived tables and subqueries unless TOP or OFFSET is also present, because those objects are conceptually unordered sets.

Where does NULL sort?

In SQL Server, NULL is treated as the lowest possible value. It comes first in ascending order and last in descending order. There is no NULLS FIRST / NULLS LAST clause in T-SQL, so you control it with a helper expression:

SQL
SELECT first_name, manager_id
FROM dbo.employees
ORDER BY CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END,  -- non-null first
         manager_id;

The editor shows the same idea. The key CASE expression sorts rows with a missing manager after everyone else.

SQL Control where NULL sorts ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Text sorting and collations

How text sorts depends on the collation, a rule set for comparing characters. The default for a new instance in English-language installations is SQL_Latin1_General_CP1_CI_AS: CI means case-insensitive, AS accent-sensitive. Under it, 'apple' and 'Apple' are equal, and sorting ignores case. Collations exist at the server, database and column level, and you can override one in a query:

SQL
SELECT name FROM dbo.products
ORDER BY name COLLATE Latin1_General_BIN2;     -- binary: uppercase before lowercase

Comparing columns that have different collations raises error 468 ("Cannot resolve the collation conflict"); fix it with COLLATE on one side. Sorting uses the collation too, so ORDER BY on large text columns can be expensive.

Paging with OFFSET ... FETCH

Applications rarely show a million rows at once. SQL Server 2012 introduced a standard way to skip rows and take a page, an extension of ORDER BY:

SQL
SELECT product_id, name, price
FROM dbo.products
ORDER BY price DESC, product_id          -- a unique tie-breaker keeps pages stable
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- rows 21 to 30

Facts to remember:

  • OFFSET requires ORDER BY; FETCH requires OFFSET.
  • ROW and ROWS, FIRST and NEXT are interchangeable.
  • The offset and fetch counts can be variables: OFFSET (@page - 1) * @size ROWS FETCH NEXT @size ROWS ONLY.
  • Include a unique column (such as the primary key) at the end of ORDER BY. If several rows tie on the sort key, their order is otherwise undefined and a row might appear on two pages or none.
  • Unlike TOP, OFFSET/FETCH cannot use WITH TIES or PERCENT.

Portable SQL spells this LIMIT 10 OFFSET 20, which the live editor uses as a labelled stand-in:

SQL Stand-in: OFFSET / FETCH as LIMIT / OFFSET ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Total count with each page

A pager usually needs the total number of rows too. A common approach is a window function, which adds the total to every row (see window functions):

SQL
SELECT product_id, name, price,
       COUNT(*) OVER () AS total_rows
FROM dbo.products
ORDER BY price DESC, product_id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Why deep offsets get slow

OFFSET 100000 ROWS still reads and discards 100,000 rows before returning the page. The deeper the page, the more work, so a "page 5000" request costs far more than "page 1". For long lists, such as infinite scroll or exports, use keyset paging (also called seek paging): remember the last key you showed and ask for rows after it.

SQL
SELECT TOP (10) product_id, name, price
FROM dbo.products
WHERE price < @last_price
   OR (price = @last_price AND product_id > @last_id)
ORDER BY price DESC, product_id;

With an index on (price DESC, product_id), SQL Server seeks straight to the start of the page no matter how deep it is. The tradeoff: keyset paging supports only "next" and "previous", not "jump to page 300". The stand-in below pages with a simple key.

SQL Keyset paging by id ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Common mistakes

  • Relying on an implicit order. Without ORDER BY, the order is not guaranteed, even if it looked sorted yesterday.
  • Paging without a unique tie-breaker. Duplicates and gaps appear between pages.
  • Sorting by a column position that later shifts when someone edits the select list.
  • Using ORDER BY in a view just to have sorted output; it is rejected (or ignored if combined with TOP 100 PERCENT). Sort in the query that uses the view.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where does NULL appear in an ascending ORDER BY in SQL Server?

    SQL Server treats NULL as the lowest value, so it sorts first in ascending order. T-SQL has no NULLS FIRST or NULLS LAST option.
  2. 2. Which statement returns rows 11 to 20 of an ordered result?

    OFFSET ... FETCH is T-SQL's paging syntax and requires ORDER BY.
  3. 3. Why add a unique column to the end of ORDER BY when paging?

    Without a unique tie-breaker, tied rows can come back in different orders, so a row may be repeated or skipped between pages.
  4. 4. What is the main weakness of OFFSET paging for very deep pages?

    The server still processes every skipped row. Keyset paging seeks directly to the next page using the last key seen.

Summary

  • ORDER BY is the only way to get a guaranteed order; ASC is the default.
  • NULL sorts lowest; use a CASE key to place it elsewhere.
  • Collations decide case and accent handling; COLLATE overrides per query.
  • OFFSET n ROWS FETCH NEXT m ROWS ONLY pages results; add a unique tie-breaker.
  • Use keyset paging for deep or endless lists.

Next: Grouping and Aggregating Data