SQL Paging in SQL Server 2012 using SQL ORDER BY OFFSET and FETCH NEXT
One of the major SQL Server 2011 features related with t-sql programming enhancements is the SQL paging using ORDER BY OFFSET n ROWS and FETCH NEXT n ROWS ONLY syntax.
In this sql tutorial, while showing one of the new t-sql features in SQL Server 2012 Denali, I will use the sample SQL database AdventureWorks download from CodePlex.
Please note that till the time Microsoft named Denali release as SQL Server 2012, it was expected to be named as SQL Server 2011 or SQL11 in the SQL Server community.
You can also search Denali features and transact-sql enhancements using SQL Server 2011 as search term.
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 BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityIDThe returned result set includes 19972 rows which is all records in Person table sorted by primary key BusinessEntityId. Nothing new in t-sql SELECT statement ORDER BY clause of the above example.
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 BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
The above t-sql Select statement returns 19962 rows (19972 -10).
Here is a screenshot where you can see the first 10 rows are not included in the return result set.
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 BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
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 = 6 -- 6th page
DECLARE @RowsCountPerPage int = 10 -- with 10 records per page
-- Returns @RowsCountPerPage rows
-- After skipping the first (@PageNumber-1)*@RowsCountPerPage rows
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS ONLY
T-SQL Paging Enhancements used in SQL Stored Procedure in SQL Server 2012
It is time to use this t-sql enhancements in Microsoft SQL Server 2012 in a sql paging stored procedure.
Below you can find a sample sql stored procedure which can be used as a t-sql paging template by sql developers.
CREATE PROCEDURE SQL_PAGING_PROCEDURE_SAMPLE
(
@PageNumber int,
@RowsCountPerPage int
)
AS
SELECT
BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS ONLY
GO
The below outputs show that how easy to implement sql paging in Microsoft SQL Server 2012, Denali databases with ORDER BY OFFSET n ROWS and FETCH NEXT n ROWS ONLY clauses.
