Analog LIMIT in MSSQL

It so happened that MSSQL does not have its counterpart to the LIMIT function in MySQL (with the exception of TOP). But quite often there is a need to choose a specific range of values ​​not from the first but for example from the 1000th.


There are several possible options:

1. Select values ​​from the first to the last needed, transfer them to the script, and already there we cut off when output those that we do not need .
')
SELECT TOP n * FROM TABLE ORDER BY id
n is the number of the last element
id - unique identifier

When working with large tables it takes a very long time, it is suitable only for small tables.

2. We make a sample with the condition of no entry on the basis of

SELECT TOP n id,* FROM TABLE
WHERE id not in (SELECT TOP (n - m) id FROM TABLE)
ORDER BY id
n is the number of the last element
m - the required number of elements
id - unique identifier

It works in some cases a little faster than the first method and is suitable for processing fairly large tables, provided it is not a complicated query.

3. Finally, the last method using row_number (), which most quickly works with almost any table

WITH num_row
AS
(
SELECT row_number() OVER (ORDER BY id) as nom , *
FROM TABLE
)
SELECT * FROM num_row
WHERE nom BETWEEN (n - m) AND n
n is the number of the last element
m - the required number of elements
id - unique identifier

Yesterday, a small paradox was noticed: sampling in method 3 worked much faster than just SELECT TOP n from the same table (in which there are more than 60 million rows).

PS This is my first topic on Habré, please do not judge strictly.