Limit and Offsetting Rows in SQL Server

Introduction

In SQL Server you might know you can select the first N rows of a query by using TOP(N), but you can also do the same and skip rows using OFFSET FETCH.

Skipping Rows

To skip rows you can use the OFFSET <N> ROWS syntax like so…

SELECT 
	 OrderId
	,CustomerID
	,OrderDate
FROM [Sales].[Orders]
ORDER BY OrderId ASC
	OFFSET 50 ROWS;

Limiting Rows

And then to limit you add FETCH NEXT <N> ROWS ONLY

SELECT 
	 OrderId
	,CustomerID
	,OrderDate
FROM [Sales].[Orders]
ORDER BY OrderId ASC
	OFFSET 50 ROWS 
	FETCH NEXT 10 ROWS ONLY;

Using Variables

You can also add variables to the query for when you need to dynamically control the number of rows skipped and returned.

DECLARE @Skip tinyint = 50
	,@Limit tinyint = 10

SELECT 
	 OrderId
	,CustomerID
	,OrderDate
FROM [Sales].[Orders]
ORDER BY OrderId ASC
	OFFSET @Skip ROWS 
	FETCH NEXT @Limit ROWS ONLY;