How do I write LINQ's .Skip(1000).Take(100) in pure SQL?

What is the SQL equivalent of the .Skip() method in LINQ?

For example: I would like to select rows 1000-1100 from a specific database table.

Is this possible with just SQL? Or do I need to select the entire table, then find the rows in memory? I'd ideally like to avoid this, if possible, since the table can be quite large.

Solution 1:

SQL Server 2012 and above have added this syntax:

SELECT *
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY

Solution 2:

In SQL Server 2005 and above you can use ROW_NUMBER function. eg.

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 51 AND 60; --BETWEEN is inclusive