SQL Server Portal

In SQL Server 2012, Microsoft has introduced a lot of new T-SQL features and enhancements and one of the best features is Paging. We have been doing this data paging in previous versions of SQL server by writing a stored procedure or a complex query.
Here is a sample, how we were using data paging in SQL Server 2005/ 2008.

USE AdventureWorks
GO
SELECT *
FROM   (SELECT ROW_NUMBER() OVER(ORDER BY EmployeeID) AS
rownumber, [FirstName], [LastName],[JobTitle] FROM HumanResources.vEmployee) AS Salaries1
WHERE  rownumber >= 10 AND rownumber = 10 AND rownumber 

But, now it is very easy to use and implement paging in SQL Server 2012. We only need to set two keywords (OFFSET, FETCH NEXT) with Order By Clause and we can get our required records.

Lets Proceed first with each keyword.

Order By Offset :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET 10 ROWS

If we use offset with order by clause, the query excludes the number of records we mentioned in OFFSET n Rows. In the above example, we used OFFSET 10 ROWS so, SQL will exclude first 10 records from the result and display the rest of all records in the defined order.

Order By Offset With FETCH NEXT :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID

OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

If we use offset with fetch next, we can define how many records we need to exclude. Also we can define that after exclusion how many records we need to pick up. In the above example, SQL excludes first 10 records and will pick up 10 records afterwards.
In other words, we can say that whenever we need to do paging we need 2 things. 1st, the page no. and 2nd the no. of records in each page. Here OFFSET is used for page number and FETCH NEXT is the number of records in each page.

Order By Fetch Next Rows Only:
If, we use Fetch Next with order by clause only without Offset, SQL will generate an error. We cannot use Fetch Next without Offset.

USE AdventureWorks2012
go
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
--OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Given below is a simple stored procedure to perform paging in SQL server 2012.

USE AdventureWorks2012
go
Create Procedure dbo.Sp_Data_Paging
@PageNo int,
@RecordsPerPage int
AS

Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET (@PageNo-1)*@RecordsPerPage ROWS
FETCH NEXT @RecordsPerPage ROWS ONLY
GO
Sp_Data_Paging 1,10 --First Page
GO
--Result

Sp_Data_Paging 2,10 --2nd Page
--Result
GO

Sp_Data_Paging 3,10 --3rd Page
--Result
GO

Conclusion :

These two keywords OFFSET and FETCH NEXT clause give boost to data pagination in SQL server 2012.
It also improves performance (because it picks up only certain records from the database) and reduces the number of codes and effort. Now the developers can do the data paging not only from front end (. NET) but also from the back end.