How to use OFFSET and FETCH in SQL Server 2012?

OFFSET and FETCH {New Addition in SQL Server 2012}

In SQL Server 2012, Microsoft has introduced many new T-SQL features and one of the best features is paging.

We have been doing this data paging in previous versions of SQL server by writing a stored procedure or a complex query.

The General syntax is as follows:

SELECT * FROM <table>

ORDER BY <columns>

OFFSET <EXPR1> ROWS

FETCH NEXT <EXPR2> ROWS ONLY

These two new arguments in the SELECT statement's ORDER BY clause that let you retrieve a fixed number of rows:

• OFFSET <EXPR1> ROWS, which you use to specify the line number from which to start retrieving results
• FETCH NEXT <EXPR2> ROWS ONLY, which you use to specify how many lines to

Order by Offset:

Have a look below query, which only using the OFFSET.

SELECT FirstName, LastName FROM Employees

ORDER BY EmployeeID --If you comment this line, query will not execute

OFFSET 5 ROWS

If we use only offset with order by clause, the query excludes the number of records we mentioned in OFFSET n Rows.

In the above example, we used OFFSET 5 ROWS; here SQL will exclude first 5 records from the result and display the rest of all records in the defined order by clause.

Order By Offset With FETCH NEXT:

Again have a look below query, which is using the OFFSET with FETCH NEXT.

SELECT EmployeeID, FirstName, LastName FROM Employees

ORDER BY EmployeeID 

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY

If we use offset with fetch next, we can define how many records we need to exclude and include in single query process.

In the above example, SQL excludes first 5 records and will pick up 5 records afterwards. In other words, we can say that whenever we need to do paging we need 2 things - 1st, the page no. and 2nd the no. of records in each page.

Here OFFSET is used for page number and FETCH NEXT is the number of records in each page.

If you use Fetch Next with order by clause without Offset, SQL will generate an error – ‘We cannot use Fetch Next without Offset’.

The ORDER BY clause is not valid in views, inline functions, derived tables, and sub-queries, unless either the TOP or OFFSET and FETCH clauses are also specified.

When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses.

OFFSET and FETCH are not supported in indexed views or in a view that is defined by using the CHECK OPTION clause.

OFFSET and FETCH can be used in any query that allows TOP and ORDER BY with the following limitations:

•  The OVER clause does not support OFFSET and FETCH.
• OFFSET and FETCH cannot be specified directly in INSERT, UPDATE, MERGE, and DELETE statements, but can be specified in a sub-query defined in these statements.
• For example, in the INSERT INTO SELECT statement, OFFSET and FETCH can be specified in the SELECT statement.
• In a query that uses UNION, EXCEPT or INTERSECT operators, OFFSET and FETCH can only be specified in the final query that specifies the order of the query results.
•  TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope

Using OFFSET and FETCH as a paging solution requires running the query one time for each "page" of data returned to the client application.

For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. 

Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state.

Note: As a best practice, expert recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.