Applying Pagination, we can easily show data from a record set in a page-wise manner. It shows a particular number of records at a time. I have used AdventureWorks Database in this example. You can download it from here: http://msftdbprodsamples.codeplex.com/
Use any one of the following queries.
Use any one of the following queries.
USE AdventureWorks
GO
DECLARE @PageNumber INT = 4
DECLARE @RowPerPage INT = 10
-- Works in SQL Server 2005 / 2008 /
2012
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY ProductID DESC) AS RowNum
FROM [Production].[Product]) AS NewTbl
WHERE NewTbl.RowNum BETWEEN
@RowPerPage*(@PageNumber-1)
+ 1 AND
@RowPerPage*@PageNumber
-- Works in SQL Server 2012
SELECT * FROM [Production].[Product] ORDER BY ProductID DESC
OFFSET
@RowPerPage*(@PageNumber-1)
ROWS
FETCH NEXT
@RowPerPage ROWS
ONLY
GO
