SQL Paging using OFFSET and FETCH

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:

Posted in SQL

Leave a Reply

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