Sql Server 2012 Pagination

Sql Server 2012 Pagination

Sql Server 2012 provide new feature Pagination. The paging is part of select statement  is an expression of order by clause . Below an example how to  use paging feature in sql server

Suppose we have table Employee

Select EmpId, EmpName,Address, Salary, city ,state from  tblEmployee  ORDER BY  EmpId OFFSET 2000 ROWS FETCH NEXT 100 ROWS ONLY

The OFFSET value can be expressed as an integer variable and so as FETCH NEXT Value which we can make them configurable so that we can manage a number of records displayed at a time and then also from the start record number from the UI.

Previously we have use Row number function for paging . If you compare this with the ROW_NUMBER() function introduced in SQL Server 2008, you can see this script is shorter and more intuitive.

The following stored procedure show paging in sql server 2012

Create Procedure EmployeeData

(

 @start int = 1

 @FetchRows int = 10

)

As

Begin

Select EmpId, EmpName,Address, Salary, city ,state from  tblEmployee  ORDER BY  EmpId OFFSET @startROWS FETCH NEXT  @FetchRows ROWS ONLY

End