Finally relaxation from Custom paging logics

Finally Sql server 2012 CTP 1 has the power to handle paging.
A new feature named Ad-hoc Query Paging is introduced in new version of Sql Server.

SQL paging using ORDER BY OFFSET n ROWS and FETCH NEXT n ROWS ONLY


In previous versions of Microsoft SQL Server, like SQL Server 2005, SQL Server 2008, or in SQL Server 2008 R2 t-sql ORDER By clause was used to sort the returned result set from the SELECT part of the query.
The following sql query is an example to how Order By clause was used before SQL Server 2012 CTP 1, Denali version.

-- Return all rows from Person table sorted by BusinessEntityID
SELECT ID, FirstName, LastName
FROM Person.Person
ORDER BY ID




It will return all records from the table




T-SQL Paging in SQL Server 2012 with ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY

Now here is OFFSET in Order By clause, this is a new t-sql feature in SQL Server 2012 CTP 1.
If Offset is used in an ORDER BY clause the result set will ignore the first offset amount rows and will not return back to the client. But the rest will be included in the result set.

-- Return all rows except the first 10 rows
SELECT ID, FirstName, LastName
FROM Person.Person
ORDER BY ID
OFFSET 10 ROWS




The above t-sql Select statement returns all rows (Total Rows - 10). This is how OFFSET 10 ROWS works in ORDER BY clause







Now an other improvement in ORDER BY clause is FETCH NEXT 10 ROWS ONLY syntax.
This new feature enables sql developers to fetch only specified amount of rows in the returned result set of the sql Select statement.
Let's say if you are doing paging with 10 rows in every page, then use Fetch Next 10 Rows Only

-- Return 10 rows after skipping the first 10 rows
SELECT ID, FirstName, LastName
FROM Person.Person
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY




Please note that Fetch Next n Rows Only cannot be used without Offset n Rows hint in Order By clause.
Otherwise new Microsoft SQL Server 2012 sql engine will throw the following error :
Msg 153, Level 15, State 2, Line 5
Invalid usage of the option NEXT in the FETCH statement.

SQL Paging with Variables used in ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY

Let's now use sql parameters for creating a more flexible t-sql paging select statement in SQL Server 2012, Denali databases.

DECLARE @PageNumber int = 3 -- 3th page
DECLARE @RowsCountPerPage int = 15 -- with 15 records per page

-- Returns @RowsCountPerPage rows
-- After skipping the first (@PageNumber - 1) * @RowsCountPerPage rows
SELECT ID, FirstName, LastName
FROM Person.Person
ORDER BY ID
OFFSET (@PageNumber - 1) * @RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS ONLY