OFFSET and FETCH: T-SQL Features in SQL Server 2012

OFFSET and FETCH: T-SQL Features in SQL Server 2012

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. 
·   
     In previous versions, to code the paging of results, complex code having NOT IN been used which is a low performance code.

·        SQL Server 2012 introduces the OFFSET command for paging or selecting for example the second highest salary from a payroll table.

·        The OFFSET with FETCH commands can limit the number of rows sent to the client. Instead of using the TOP command for paging, these new commands can be used to reduce complexity. These new commands will normally be accompanied with ORDER BY.

Example: select the third page with each page having 5 records
Using previous Versions of SQL Server:

SELECT TOP (5) ProductID, Name

FROM AdventureWorks.Production.Product

WHERE ProductID NOT IN (SELECT TOP (10) ProductID FROM 

AdventureWorks.Production.Product ORDER BY NAME)

ORDER BY NAME

Using SQL Server 2012:

SELECT ProductID, Name

FROM AdventureWorks.Production.Product

ORDER BY NAME

OFFSET 10 ROWS

FETCH NEXT 5 ROWS ONLY

The above query retrieves the data present in third page.

OFFSET: Specifies the number of rows to skip before it starts to return rows from the query.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed.

FIRST and NEXT are synonyms and can be used anyone to fetch the records.
ROW and ROWS are synonyms and can be used anyone to fetch the records.
Rules to use OFFSET FETCH:

·         ORDER BY is mandatory to use OFFSET and FETCH clause.

·         OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

·         TOP cannot be combined with OFFSET and FETCH in the same query expression.

·         The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

If you have any queries or suggestions, please feel free to ask in comments section.