Is There a Direct SQL Equivalent to LINQ's Skip() and Take()?

Skip and Take in SQL: A LINQ Equivalent

LINQ's .Skip() and .Take() methods provide a convenient way to work with a subset of data in memory. However, in situations where the underlying data source is a large SQL database, it's often preferable to perform these operations directly in SQL rather than in memory. This avoids the overhead of transferring large amounts of data into memory.

One such scenario is when you need to select a specific range of rows from a database table. LINQ provides the .Skip() and .Take() methods for this purpose, but is there a direct SQL equivalent?

SQL Server 2012 and Above

SQL Server 2012 and above introduced a new syntax for row offsetting and limiting:

OFFSET (@Skip) ROWS
FETCH NEXT (@Take) ROWS ONLY

To select rows 1000-1100 from a specific table, you would use the following query:

SELECT *
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate
OFFSET 1000 ROWS
FETCH NEXT 100 ROWS ONLY

This query will perform the following steps:

1. Sort the 'Sales.SalesOrderHeader' table in ascending order by the 'OrderDate' column.
2. Skip the first 1000 rows using the 'OFFSET' clause.
3. Take the next 100 rows using the 'FETCH NEXT' clause.

This solution is much more efficient than performing the same operation in memory, as it avoid the need to select all the rows, only to then discard the first 1000 and select the next 100 manually.

The above is the detailed content of Is There a Direct SQL Equivalent to LINQ's Skip() and Take()?. For more information, please follow other related articles on the PHP Chinese website!