Is there a SQL equivalent to Linq .Skip(1000).Take(100)

Question

Is there a SQL equivalent to the Skip method of Linq?

For example I would like to be able to select the 1000 - 1100 rows from a database table.

Or am I going to have to select the whole table and then find the rows in memory? My problem is that the table can be quite big and I'd like to avoid doing that if possible.

Answer 1

No, but you could emulate MySQL's LIMIT clause (Stack Overflow link) to achieve the same result.

Answer 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 50 AND 60;

Answer 3

Do this:

Run .Skip(1000).Take(100) on a LINQ to SQL datacontext and look at the SQL output. It will generate a SQL statement for you that does what you're describing.

It won't be as elegant but it gets the job done.

Answer 4

you can use rownumber.

rownumber

Answer 5

LINQ to SQL does this by using a ROW_NUMBER windowing function:

  SELECT a,b,c FROM 
   (SELECT a,b,c, ROW_NUMBER() OVER (ORDER BY ...) as row_number
    FROM Table) t0
   WHERE to.row_number BETWEEN 1000 and 1100;

This works, but the need to manufacture the row_number from the ORDER BY may result in your query being sorted on the server side and cause performance problems. Even when an index can satisfy the ORDER BY requirement, the query still has to count 1000 rows before startting to return results. All too often developers forget this and just throw a pagination control over a 5 mil rows table and wonder why the first page is returned so much faster than the last one...

None the less, using ROW_NUMBER() is probably the best balance between ease of use and good performance, provided you make sure you avoid the sort (the ORDER BY condition can be satisified by an index).