
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;