In my earlier post i have wrote about new feature  "Order By Clause Enhancement". I have given demonstration with examples as well as how can we use this feature.

It is used for SQL pagination and also explained for the same for pagination.

You can read that post here http://beyondrelational.com/blogs/paresh/archive/2011/04/28/sql-pagination-with-order-by-clause-enhancement-sql-server-denali.aspx. 

Here i want to give same demonstration but not with only Order By Clause enhancement, It will be various ways and methods for SQL pagination.  We will look for the native method and new method of SQL pagination and review it. 

USE PaginationDB
GO

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

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

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 1000000
GO

Inserting records using sequence and looks for inserted records.

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

We will fetch 10 records using various methods, Using TempTable :

CREATE TABLE #PaginationTemp
(
Id INT IDENTITY(1,1),
PageId INT,
PageName VARCHAR(100)
)

INSERT INTO #PaginationTemp(PageId, PageName)
SELECT 
PageId, 
PageName 
FROM PaginationTable
ORDER BY PageName,PageId

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

SELECT * FROM #PaginationTemp
WHERE Id BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

Using query alias: 

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

SELECT res.PageId, res.PageName
FROM
(
SELECT 
ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,
PageId, 
PageName 
FROM PaginationTable
) res
WHERE RES.RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

Using Common Table Expression :

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

;With CTEPage
AS
(
SELECT 
ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,
PageId, 
PageName 
FROM PaginationTable
) 

SELECT PageId, PageName FROM CTEPage
WHERE RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

Using OFFSET and FETCH NEXT and Order by introduced by SQL Server Denali :

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

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

GO

I got one suggestion from Jacob(blog) , and he had a good and optimized suggestion for SQL Pagination as we should put TOP clause in script same as page size, so it will filter rows as per search criteria. Like we have script using TOP clause and revised script is as following,

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

INSERT INTO #PaginationTemp(PageId, PageName)
SELECT 
TOP (((@PageIndex - 1) * @Pagesize) + @Pagesize)
PageId, 
PageName 
FROM PaginationTable
ORDER BY PageName,PageId

SELECT * FROM #PaginationTemp
WHERE Id BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

Here you have same output for all above such methods including last optimized suggested by Jacob used for SQL pagination.

You can share your ideas if you have a new methods for pagination.