Equivalent of LIMIT and OFFSET for SQL Server?

@nombre_row :nombre ligne par page
@page:numero de la page

//--------------code sql---------------

declare @page int,@nombre_row int;
set @page='2';
set @nombre_row=5;
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY etudiant_ID ) AS RowNum, *
FROM etudiant

) AS RowConstrainedResult
WHERE RowNum >= ((@page-1)*@nombre_row)+1
AND RowNum < ((@page)*@nombre_row)+1
ORDER BY RowNum

Since nobody provided this code yet:

SELECT TOP @limit f1, f2, f3...
FROM t1
WHERE c1 = v1, c2 > v2...
AND
t1.id NOT IN
(SELECT TOP @offset id
FROM t1
WHERE c1 = v1, c2 > v2...
ORDER BY o1, o2...)
ORDER BY o1, o2...

Important points:

- **ORDER BY must be identical**
- `@limit` can be replaced with number of results to retrieve,
- `@offset` is number of results to skip
- Please compare performance with previous solutions as they may be more efficient
- this solution duplicates `where` and `order by` clauses, and will provide incorrect results if they are out of sync
- on the other hand `order by` is there explicitly if that's what's needed

For me the use of OFFSET and FETCH together was slow, so I used a combination of TOP and OFFSET like this (which was faster):

SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

**Note:** If you use TOP and OFFSET together in the same query like:

SELECT TOP 20 columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS

Then you get an error, so for use TOP and OFFSET together you need to separate it with a sub-query.

And if you need to use SELECT DISTINCT then the query is like:

SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname


**Note:** The use of SELECT ROW_NUMBER with DISTINCT did not work for me.

-- @RowsPerPage can be a fixed number and @PageNumber number can be passed
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2

SELECT *

FROM MemberEmployeeData

ORDER BY EmployeeNumber

OFFSET @PageNumber*@RowsPerPage ROWS

FETCH NEXT 10 ROWS ONLY

Specifically for SQL-SERVER you can achieve that in many different ways.For given real example we took Customer table here.

***Example 1: With "SET ROWCOUNT"***

SET ROWCOUNT 10
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName

***To return all rows, set ROWCOUNT to 0***

SET ROWCOUNT 0
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName

***Example 2: With "ROW_NUMBER and OVER"***

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 0 and 10

***Example 3 : With "OFFSET and FETCH", But with this "ORDER BY" is mandatory***

SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
Hope this helps you.

Since, I test more times this script more useful by 1 million records each page 100 records with pagination work faster my PC execute this script 0 sec while compare with mysql have own limit and offset about 4.5 sec to get the result.

Someone may miss understanding Row_Number() always sort by specific field. In case we need to define only row in sequence should use:

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
---
SELECT TOP {LIMIT} * FROM (
SELECT TOP {LIMIT} + {OFFSET} ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ROW_NO,*
FROM {TABLE_NAME}
) XX WHERE ROW_NO > {OFFSET}

Explain:

- {LIMIT}: Number of records for each page
- {OFFSET}: Number of skip records

This feature is now made easy in SQL Server 2012.
This is working from SQL Server 2012 onwards.

Limit with offset to select 11 to 20 rows in SQL Server:

SELECT email FROM emailTable
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

* `ORDER BY`: required
* `OFFSET`: optional number of skipped rows
* `NEXT`: required number of next rows

Reference:

[To see links please register here]

Elaborating the [Somnath-Muluk][1]'s answer just use:

SELECT *
FROM table_name_here
ORDER BY (SELECT NULL AS NOORDER)
OFFSET 9 ROWS
FETCH NEXT 25 ROWS ONLY

w/o adding any extra column.
Tested in SQL Server 2019, but I guess could work in older ones as well.


[1]:

[To see links please register here]

I assume that, In C# Expression/LINQ statement of skip and take generating below SQL Command


DECLARE @p0 Int = 1
DECLARE @p1 Int = 3
SELECT [t1].[Id]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Id]
FROM [ShoppingCart] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]