OFFSET (@PageNumber-1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLYSo let's say I want to skip beyond the first 40 rows (based on Vendor name) and fetch the next 10...
use AdventureWorks2012 go declare @rownum int = 40 SELECT Vendor.Name, row_number() over (order by Name) as RowNum FROM Purchasing.Vendor ORDER BY Name OFFSET (@RowNum) ROWS -- at row #41, grab the next 40 + 10 rows FETCH NEXT (@RowNum + 10) ROWS ONLY;For those who want to pass in a "page number" (say, jump to page 5 on a web page that shows X number of rows at a time)...
declare @PageNumber int =5 declare @RowsPerPage int = 10 select BusinessEntityID, AccountNumber, Name, ROW_NUMBER() OVER (ORDER BY Name) as RowNum from Purchasing.Vendor Order by Name offset (@PageNumber-1) * @RowsPerPage rows fetch next @RowsPerPage rows onlyIt won't generate an execution plan that's any better than techniques prior to SQL 2012, but (IMO) makes the approach more straightforward.