The TOP and OFFSET-FETCH clauses allow you to limit the number of rows returned by a query.
Often, when we run a query, we don’t want to get all the records that meet a condition.
Imagine you want to know “Who are the 3 employees who sold the most this month?” or you are programming a web page and need to show the results of “Page 2” (from record 11 to 20).
For these scenarios, T-SQL offers us two tools, TOP and the modern, standard OFFSET-FETCH.
Which one should I use?
| Feature | TOP | OFFSET-FETCH |
|---|---|---|
| Main Use | ”The top N”, quick sampling, subqueries. | Web application pagination. |
| SQL Standard | No (Microsoft proprietary). | Yes (ANSI SQL). |
| Requires ORDER BY | No (but recommended). | Yes (Mandatory). |
| Supports Ties | Yes (WITH TIES). | No. |
| Supports Percentage | Yes (PERCENT). | No. |
- Use TOP when you want to analyze extremes (highest prices, latest dates) or need to handle ties.
- Use OFFSET-FETCH when you are building an API or a web page that needs “Next page” buttons.
The TOP Clause
TOP is SQL Server’s traditional way to limit results. It’s very simple: you tell it how many rows you want and it stops as soon as it reaches that number.
TOP with a fixed number
This is the most common use. “Give me the first 5”.
SELECT TOP (5) Nombre, Precio
FROM Productos
ORDER BY Precio DESC;
Important: TOP and ORDER BY should always go hand in hand. If you use TOP without an ORDER BY, SQL Server will return 5 “random” rows (based on how they are physically on disk or in cache), which makes your query non-deterministic.
TOP with percentage (PERCENT)
Instead of a fixed number, we can ask for a proportion of the data.
-- Get the 10% of employees with the worst sales
SELECT TOP (10) PERCENT Nombre, Ventas
FROM Empleados
ORDER BY Ventas ASC;
If the table has 100 rows, it will return 10. If it has 105, it will return 11 (it always rounds up to meet the percentage).
Pagination with OFFSET-FETCH
TOP is great, but it has a problem: it only knows how to take data from the beginning. It doesn’t have an easy native way to say “skip the first 10 and give me the next 10”.
Historically, doing pagination in SQL Server was a headache (using functions like ROW_NUMBER). Fortunately, since SQL Server 2012 we have OFFSET-FETCH.
This syntax is part of the ANSI SQL standard (meaning it’s similar in Oracle or PostgreSQL) and is specifically designed for paginating results.
Syntax
It works as an extension of the ORDER BY clause.
SELECT Columnas
FROM Tabla
ORDER BY Columna
OFFSET X ROWS -- How many rows to skip
FETCH NEXT Y ROWS ONLY -- How many rows to take
Pagination Example
Let’s imagine we show 10 products per page.
Page 1 (First 10): We could use TOP, but with OFFSET it would be:
SELECT ProductoID, Nombre
FROM Productos
ORDER BY ProductoID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;
OFFSET-FETCH mandatorily requires the ORDER BY clause. You cannot paginate something that has no order.
Page 2 (Skip 10, take the next 10):
SELECT ProductoID, Nombre
FROM Productos
ORDER BY ProductoID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
Page 3 (Skip 20, take 10):
SELECT ProductoID, Nombre
FROM Productos
ORDER BY ProductoID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
- Standard: It’s portable code.
- Clarity: The purpose of the code is obvious (to paginate).
- Flexible: You can use only
OFFSETwithoutFETCH(to say “give me all except the first 5”), although it’s rare to use it that way.
