In SQL Server , you can use the OFFSET and FETCH and apply paging and retreive N records at a time. OFFSET AND FETCH clause are part of the ORDER BY clause and hence you must include the ORDER BY clause.
For example , you might want to skip the first 4 records and retrieve 5 records from the query , you could use the following
This query retrieves all the records from the Department table.
Use AdventureWorks2014 GO SELECT * FROM HumanResources.Department
Now , we can use the below query to skip the first 4 records and get the next 5 records from the Department table.
Use AdventureWorks2014 GO SELECT * FROM HumanResources.Department ORDER BY Name OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY;