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

Question

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.

score 154 · Accepted Answer · answered Oct 29 '13 at 21:09

154

SQL Server 2012 and above have added this syntax:

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

answered Oct 29 '13 at 21:09

John Gietzen

48,783
32
145
190

14

Note that you need to use ORDER BY ___ in order to use OFFSET command....not that you should ever try paginating without an order. – James Haug Jul 21 '16 at 23:24
Also note the that the 'new' syntax strangely have a performance penalty linear with the @skip! The row_number approach does NOT have this (only tested on indexed order). For lo @Skip less about 20, the new syntax is faster than the row_number approach though. – Eske Rahn Jun 24 '18 at 14:23

score 79 · Answer 2 · edited Feb 19 '13 at 16:48

79

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

edited Feb 19 '13 at 16:48

Drew Miller

675
6
15

answered Nov 16 '09 at 21:00

Dan Diplo

25,076
4
67
89

See the link in my answer for a bit more detail. http://stackoverflow.com/questions/1744802/is-there-a-sql-equivilant-to-linq-skip1000-take100/1744815#1744815 – Mike Atlas Nov 16 '09 at 21:06
BETWEEN 51 and 60 - it's inclusive. – Drew Miller Feb 19 '13 at 16:41
1

But this will first select all and then from that selection take only 10 right? Or will the first query/view have only 10 already? – tedi Jan 29 '18 at 13:53

score 22 · Answer 3 · answered Nov 16 '09 at 21:03

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).

Thanks for the extra performance info, will have to be careful and test it. — Ray, Nov 16 '09 at 21:16
Tested and for my half million row table, that last page is about 7 times slower than the first page. Not ideal, but acceptable for me. — Ray, Nov 23 '09 at 01:59

score 9 · Answer 4 · answered Sep 23 '16 at 15:54

Try this one:

select * from [Table-Name] order by [Column-Name] 
offset [Skip-Count] rows
FETCH NEXT [Take-Count] rows only

Example:

select * from Personals order by Id
offset 10 rows            --------->Skip 10
FETCH NEXT 15 rows only   --------->Take 15

score 4 · Answer 5 · answered Nov 16 '09 at 21:00

4

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.

answered Nov 16 '09 at 21:00

Joseph

25,330
8
76
125

2

Not what was being asked. – RayLoveless Sep 09 '16 at 16:19

score 2 · Answer 6 · edited May 23 '17 at 12:02

2

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

edited May 23 '17 at 12:02

Community

1
1

answered Nov 16 '09 at 20:58

Mike Atlas

8,193
4
46
62

1

Accepted answer there points to an interesting CodeProject link, ["Paging of Large Resultsets in ASP.NET"](http://www.codeproject.com/Articles/6936/Paging-of-Large-Resultsets-in-ASP-NET) (more SQL oriented than the name suggests). – ruffin Jan 17 '15 at 02:36

score 0 · Answer 7 · answered Aug 01 '23 at 18:07

0

Linqpad also has a SQL view you could use to get the SQL:

answered Aug 01 '23 at 18:07

Zachary Scott

20,968
35
123
205

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

7 Answers7

Linked