In this article we will learn about SQL Paging using OFFSET and FETCH. In SQL these clauses can be used with ORDER BY to limit the number of rows returned by a query.

OFFSET:  Specifies how many rows to skip over before starting to return rows.

FETCH:   Specifies how many rows to return.

Following query will return 200 records in one go @PageNumber means it will skip 0 records and will start picking from first record. @RowsOfPage will say how many records need to fetch.

DECLARE @PageNumber INT = 0
DECLARE @RowsOfPage INT = 200

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO
SQL Paging using OFFSET and FETCH

Changing the OFFSET to 200 will give us the next 200 results. Like show in below query

DECLARE @PageNumber INT= 200
DECLARE @RowsOfPage INT = 200

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO

In below query you need to play with @PageNumber and @RowsOfPage. You can pass these parameter form application and SQL query will return the desired set of records in paging.

DECLARE @PageNumber INT= 1  -- this is the page number whose data you want  
DECLARE @RowsOfPage INT = 200  -- this is number of records you want in return.   
DECLARE @MaxPagesInTable FLOAT

Select @MaxPagesInTable=count(1)  FROM [NorthWND].[dbo].[Orders]
SET @MaxPagesInTable = CEILING(@MaxPagesInTable / @RowsOfPage)
print @MaxPagesInTable

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 

OFFSET (@PageNumber - 1) * @RowsOfPage ROWS 
FETCH NEXT @RowsOfPage ROWS ONLY

GO

 I hope you have enjoyed reading this article and learned about “SQL Paging using OFFSET and FETCH”. You can use above SQL sample query to implement paging in your application. You can also visit Microsoft learn or for more such articles you can refer our SQL Section. 

You may also like:

Leave a Reply

Your email address will not be published. Required fields are marked *

Explore More

How to enable SQL Server authentication

SQL Server Authentication is a default way to connect to MS SQL Server, but there may be many times that you need to use SQL Server Authentication to connect to

Read More

Set Transaction Isolation Level in SQL 

In this post we’ll discuss SQL server “Set Transaction Isolation Level”. These are of 5 types, and we’ll discuss all one by one below. READ UNCOMMITTED Read uncommitted specifies that

Read More

Is SQL NOLOCK bad practice?

In this post we’ll read about the use of [Nolock] in SQL Queries and stored procedures. Is this good or bad practice to use this in query. Let’s check out

Read More