Equivalent of LIMIT and OFFSET for SQL Server?

Question

In PostgreSQL there is the Limit and Offset keywords which will allow very easy pagination of result sets.

What is the equivalent syntax for SQL Server?

Armchair

For sql server 2012, this feature is implemented in easy way. See my answer – Crosscrosslet

Thanks for asking this question, we are being forced to transition from MySQL to MsSQL :( – Delaminate

OFFSET / FETCH in ORDER CLAUSE is the SQL ISO standard. LIMIT and TOP are vendor solutions and are not portable between different RDBMS – Gallice

One point pertinent to note is that "Order By" clause is mandatory while using Offset Fetch – Pimento

Answer 1

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

Fecula

Old now. Sql Server 2012 and later support OFFSET/FETCH – Porcupine

@JoelCoehoorn Not old. I just got assigned to project using SLQ Server 2008 having used only mysql in the past... – Nickeliferous

This is quite good but needs to be ajusted a little WHERE RowNum >= (@Offset + 1) – Hyperspace

@EricHerlitz: Please refer to the note at the end of this answer. – Fecula

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. MSSQL2008 R2. – Overpower

The order by won't work because of the WITH unfortunately. – Cleek

@EricHerlitz don't complicate this even more.. just use WHERE RowNum > @Offset and we're good with the start condition. – Empedocles

@Fecula If my Table has 200k records, it will fetch all first, then apply limit ? Is this query efficient ? – Phrenetic

anyone knows how to do left join on this query? – Waiter

RowNum >= (@Offset+1) AND RowNum < (@Offset+1) + @Limit – Morelock

I compared the results of this query with SELECT * FROM Table WHERE <whatever> ORDER BY (SortCol1, SortCol2, ...) ASC OFFSET *Offset ROWS FETCH NEXT *Limit ROWS ONLY; And both seem to work similarly with same efficiency. Does this query provide any additional benefit ? Also to note, the efficiency of both queries start to drop when *Offset increases. @Phrenetic – Forgo

Answer 2

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: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql

Crosscrosslet

Is there an equiv of SQL_CALC_FOUND_ROWS when using this? – Wizardly

@Wizardly @@Rowcount will give you that I think – Culvert

GOTCHA: You can't use this from within a CTE. It has to be used in the main query. I wanted to limit the amount of rows returned (pagination) and then perform an expensive calculation to the 10 or so rows returned, rather than determine the rows, perform the expensive computation, and then skip/take what I needed. @Aaronaught's answer will work for those needing to restrict rows within a CTE. – Teaspoon

@SarojShrestha: This is not Offset and Fetch issue. You should revisit architecture of your table now. Consider Partitioning of tables, your data row and it's different column types and total table size, consider archiving some rows if not required regularly, check your server specs. – Crosscrosslet

Answer 3

select top {LIMIT HERE} * from (
      select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n 
      from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
) xx where r_n_n >={OFFSET HERE}

A note: This solution will only work in SQL Server 2005 or above, since this was when ROW_NUMBER() was implemented.

Bedesman

I've been using this query for a little while now and it works great so thanks for that. I'm just wondering what the 'xx' represents? – Summersault

the sub query requires a name. as I'm not using it just put xx there – Bedesman

The xx is just a table alias. It might be a little clearer if you said AS xx – Swanhilda

anyone knows how to do left join on this query? – Waiter

Answer 4

You can use ROW_NUMBER in a Common Table Expression to achieve this.

;WITH My_CTE AS
(
     SELECT
          col1,
          col2,
          ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
)
SELECT
     col1,
     col2
FROM
     My_CTE
WHERE
     row_number BETWEEN @start_row AND @end_row

Cosmogony

Wondering if SELECT inside CTE will load whole table first and then outer WHERE will filter out not needed records? Or SQL will optimize it and avoid loading content of whole My_Table? I am asking because if it doesn't and table has lot's of records it may be inefficient. – Perceptible

Normally SQL Server will optimize around CTEs when it can. Because this is doing a BETWEEN I don't know if it will or not. I'm on my phone, so I can't test it right now, but it should be easy enough to test. Just make sure that you use a large enough dataset that SQL doesn't just load the whole table anyway and try using a BETWEEN that starts somewhere past the first row. – Cosmogony

Answer 5

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.

Noncooperation

FWIW, Microsoft's documentation specifically recommends using only OFFSET and FETCH: "We recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application." – Isadoraisadore

Answer 6

-- @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

Sudatory

Works for Microsoft SQL Server 13.x Thanks a lot . – Nannana

work for me, but instead of FETCH NEXT 10 ROWS ONLY I used this method: FETCH NEXT @RowsPerPage ROWS ONLY – Hyperextension

Answer 7

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.

Readjust

I get "A TOP can not be used in the same query or sub-query as a OFFSET." – Tress

You are right @MichaelRushton, can not be used in the same query or in the same sub-query, then you have to use a sub-query to separate it. So if you have the SQL like SELECT TOP 20 id FROM table1 where id > 10 order by date OFFSET 20 rows, you must transform it like SELECT TOP 20 * FROM (SELECT id FROM table1 where id > 10 order by date OFFSET 20 ROWS) t1. I will edit my answer. Thanks and excuse me my English. – Readjust

Answer 8

Adding a slight variation on Aaronaught's solution, I typically parametrize page number (@PageNum) and page size (@PageSize). This way each page click event just sends in the requested page number along with a configurable page size:

begin
    with My_CTE  as
    (
         SELECT col1,
              ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
    )
    select * from My_CTE
            WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1) 
                              AND @PageNum * @PageSize

end

Raffle

Answer 9

Another sample :

declare @limit int 
declare @offset int 
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int 
declare @idxfim int 
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
    (
        SELECT 
             ROW_NUMBER() OVER (order by object_id) AS rowid, *
        FROM 
            sys.objects 
    )
select *
    from 
        (select COUNT(1) as rowqtd from paging) qtd, 
            paging 
    where 
        rowid between @idxini and @idxfim
    order by 
        rowid;

Disarticulate

I removed your anti-microsoft hate speech. Don't discuss holy wars here; just answer and ask questions in a non-subjective way. – Armchair

Answer 10

There is here someone telling about this feature in sql 2011, its sad they choose a little different keyword "OFFSET / FETCH" but its not standart then ok.

Micaelamicah

Answer 11

The closest I could make is

select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber  and ct <= toNumber

Which I guess similar to select * from [db].[dbo].[table] LIMIT 0, 10

Acaudal

Answer 12

Elaborating the Somnath-Muluk'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.

Ubiety

Answer 13

select top (@TakeCount) * --FETCH NEXT
from(
    Select  ROW_NUMBER() OVER (order by StartDate) AS rowid,*
    From YourTable
)A
where Rowid>@SkipCount --OFFSET

Alisander

Answer 14

@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

Cyrie

ROW_NUMBER() OVER is a slow process to get results... – Pangermanism

Answer 15

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

Odometer

Answer 16

In SQL server you would use TOP together with ROW_NUMBER()

Scale

Answer 17

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]

Stentorian

Answer 18

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

Sabin

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – Crayfish