SQL Server – Working with OFFSET (paging in SQL server )

SQL Server – Working with OFFSET (paging in SQL server )

Introduction 

In this article I am explaining about how to access the table rows between 2 intervals. Later versions we have use logics to get these type of data, but in SQL server 2012 onwards very simple to get this data with simple queries 

For Example:- 

I am using the AdventureWorks2012  data base

SELECT ROW_NUMBER()OVER(ORDER BY p.Name) AS Sno,* FROM Production.Product p ORDER BY p.Name

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 10 rows FETCH NEXT 5 ROWS ONLY

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 5 rows FETCH NEXT 5 ROWS ONLY

In the above query having the key words like “OFFSET” is nothing starts with and “FETCH NEXT” mean how many rows to fetch from the table 

For example 

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 5 rows FETCH NEXT 5 ROWS ONLY

“OFFSET” get the records from 6^th row to “FETCH NEXT” next  5 rows .