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;

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.