From SQL Server 2012, Microsoft introduced a couple of T-SQL Enhancements. One of them is Query Pagination. In most of the applications, a common requirement is how to fetch the data from the database servers as page wise.
In earlier versions of SQL Server like SQL Server 2005/2008/R2, we can implement Pagination by using different techniques like ROW_NUMBER() function or CTE – Common Table Expression. where as n SQL Server 2012, Microsoft has introduced Pagination as a part of Select query in a Order By clause. Now you will have to use OFFSET and FETCH NEXT with the order by clause.
For this demonstration i am using AdventureWorks2008 , I have created a Stored Procedure which takes two parameters. First parameter takes the page number and the second parameter no. of records for that page.
below is The stored procedure:
create procedure GetRecords
declare @skiprows int
set @skiprows=@Total_Records * (@Page_Number-1)
select * from Sales.Customer
order by CustomerID ASC offset @skiprows rows
fetch next @Total_Records Rows only
exec GetRecords 1,10
exec GetRecords 2,10
We hope you find the tip as helpful, I hope you liked the article. Please share your valuable suggestions and feedback in the comments