ORDER BY Clause Enhancement and Pagination in SQL Server 2011

In earlier version of SQL server, when we need to SQL pagination then we were using temp table, Common Table expression or Ranking functions.

One best feature introduced by SQL Server 2011 and that enhancement is with ORDER BY clause.
Yes this is the feature introduced by SQL Server Denali and we can use this option to get the pagination.

We can specify the Order by clause with OFFSET and FETCH NEXT keywords. Definition looks as following.

OFFSET : Determine the start row to begin on.
FETCH NEXT : How many rows need to fetch.

Let's look on some examples of ORDER BY clause with OFFSET and FETCH NEXT.

Creating Database and table objects.

CREATE DATABASE PaginationDB
GO

use PaginationDB
GO

IF (OBJECT_ID('PaginationTable') > 0)
DROP TABLE PaginationTable
GO

CREATE table PaginationTable
(
PageId INT,
PageName varchar(100)
)
GO

Creating Sequence

IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence')
DROP SEQUENCE NewSequence;
GO

CREATE SEQUENCE [NewSequence] AS INT
START with 1
INCREMENT by 1
MAXVALUE 1000
GO

Inserting records using sequence and looks for inserted records.

INSERT INTO PaginationTable
SELECT next value for [NewSequence], name FROM sys.objects
GO

SELECT PageId,PageName FROM PaginationTable
GO

Let us fetch first 10 records from table using OFFSET and FETCH NEXT

-- Fetch first 10 rows
DECLARE @PageIndex INT = 1, @Pagesize INT = 10

SELECT PageId, PageName FROM PaginationTable
ORDER BY PageId 
OFFSET (@PageIndex - 1) * @Pagesize ROWS
FETCH NEXT @Pagesize ROWS ONLY
GO

-- Fetch next 10 rows
DECLARE @PageIndex INT = 2, @Pagesize INT = 10

SELECT PageId, PageName FROM PaginationTable
ORDER BY PageId 
OFFSET (@PageIndex - 1) * @Pagesize ROWS
FETCH NEXT @Pagesize ROWS ONLY
GO

-- Fetch Last rows
DECLARE @PageIndex INT = 1, @Pagesize INT = 10
DECLARE @TotalRows INT = (SELECT COUNT(1) FROM PaginationTable )

SELECT PageId, PageName FROM PaginationTable
ORDER BY PageId 
OFFSET (@TotalRows / @Pagesize ) * @Pagesize ROWS
FETCH NEXT @Pagesize ROWS ONLY
GO

I hope you already started this new enhancement of Order By clause to implement pagination.