Analog LIMIT in MSSQL

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


    There are several possible options:

    1. We select the values ​​from the first to the last needed, transfer them to the script and there we cut off those that we do not need when outputting .

    SELECT TOP n * FROM TABLE ORDER BY id
    n - number of the last element
    id - unique identifier

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

    2. We make a selection with the condition of not entering on the basis of n - number of the last element m - required number of elements id - unique identifier

    SELECT TOP n id,* FROM TABLE
    WHERE id not in (SELECT TOP (n - m) id FROM TABLE)
    ORDER BY id





    It works in some cases a little faster than the first method and is suitable for processing large enough tables under the condition of a non-complex query

    3. Finally, the last method using row_number (), which works most quickly with almost any table n - the number of the last element m - the required number of id elements - unique identifier A small paradox was noticed yesterday; 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 the hub, please do not judge strictly.

    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








    Also popular now: