SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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 BusinessEntityID
Code

The 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.

SQL Server 2012 Order By clause Ad-Hoc Paging with Offset and Fetch Next


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
Code

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.

SQL Ad-hoc paging skip 10 rows using Order By Offset

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
Code

SQL paging Order By Offset Fetch Next

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
Code

SQL paging in SQL Server 2012 TSQL enhancements


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
Code

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.

T-SQL paging procedure sample first page T-SQL paging procedure sample second page
T-SQL paging procedure sample page 3



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.