T-SQL features in SQL Server 2012 – OFFSET and FETCH

Problem:

In the past, when you want to use a form to show the results from the SQL Server database or want to show it in the GridView Control in ASP.Net for example, you will face the problem of trying to get specific number of row into form with specific number of size, and is needed a lot of numbers of code lines because you will do that in the manual way.

Solution:

Now the new features in SQL Server 2012, you can use OFFSET and FETCH feature that allows filtering a requested range of rows with a specific order and the number of rows to skip or return.

The new solution is quite useful for help us to getting the required output, paging in a simple and effective way and many of several purposes.

Syntax:

SELECT * FROM <table> ORDER BY <columns>

OFFSET <EXPR1> ROWS FETCH NEXT <EXPR2> ROWS ONLY

Examples:

SELECT * FROM <table> FETCH FIRST ROW ONLY

SELECT * FROM <table> ORDER BY <columns> OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

SELECT * FROM <table> OFFSET 100 ROWS

Technet.microsoft.com Examples:

1- Skip first 10 rows from the sorted result set and return the remaining rows.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

2- Skip first 10 rows from the sorted resultset and return next 5 rows.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;