Sql server® 2012 t-sql fundamentals

The 

OFFSET-FETCH

 Filter

The TOP option is a very practical type of filter, but it has two shortcomings—it’s not standard, and it 

doesn’t support skipping capabilities. Standard SQL defines a TOP-like filter called OFFSET-FETCH 

that does support skipping capabilities, and this makes it very useful for ad-hoc paging purposes. 

SQL Server 2012 introduces support for the OFFSET-FETCH filter.

The OFFSET-FETCH filter in SQL Server 2012 is considered part of the ORDER BY clause, which 

normally serves a presentation ordering purpose. By using the OFFSET clause, you can indicate how 

many rows to skip, and by using the FETCH clause, you can indicate how many rows to filter after the 

skipped rows. As an example, consider the following query.

SELECT orderid, orderdate, custid, empid 

FROM Sales.Orders 

ORDER BY orderdate, orderid 

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

The query orders the rows from the Orders table based on orderdate, orderid ordering (from least 

to most recent, with orderid as the tiebreaker). Based on this ordering, the OFFSET clause skips the 

first 50 rows, and the FETCH clause filters the next 25 rows only.

www.it-ebooks.info

---

48  

Microsoft SQL Server 2012 T-SQL Fundamentals

Note that a query that uses OFFSET-FETCH must have an ORDER BY clause. Also, the FETCH clause 

isn’t supported without an OFFSET clause. If you do not want to skip any rows but do want to filter 

with FETCH, you must indicate that by using OFFSET 0 ROWS. However, OFFSET without FETCH is al-

lowed. In such a case, the query skips the indicated number of rows and returns all remaining rows in 

the result.

There are interesting language aspects to note about the syntax for OFFSET-FETCH. The singular 

and plural forms ROW and ROWS are interchangeable. The idea is to allow you to phrase the filter 

in an intuitive English-like manner. For example, suppose you wanted to fetch only one row; though 

it would be syntactically valid, it would nevertheless look strange if you specified FETCH 1 ROWS. 

Therefore, you’re allowed to use the form FETCH 1 ROW. The same applies to the OFFSET clause. Also, 

if you’re not skipping any rows (OFFSET 0 ROWS), you may find the term “first” more suitable than 

“next.” Hence, the forms FIRST and NEXT are interchangeable.

As you can see, the OFFSET-FETCH clause is more flexible than TOP in the sense that it supports 

skipping capabilities. However, OFFSET-FETCH doesn’t support the PERCENT and WITH TIES options 

that TOP does. Because OFFSET-FETCH is standard and TOP isn’t, I recommend using OFFSET-FETCH 

as your default choice, unless you need the capabilities that TOP supports and OFFSET-FETCH doesn’t.