[sql] LIMIT 10..20 in SQL Server

I'm trying to do something like :

SELECT * FROM table LIMIT 10,20

or

SELECT * FROM table LIMIT 10 OFFSET 10

but using SQL Server

The only solution I found looks like overkill:

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases 
 ) a WHERE row > 5 and row <= 10

I also found:

SELECT TOP 10 * FROM stuff; 

... but it's not what I want to do since I can't specify the starting limit.

Is there another way for me to do that ?

Also, just curious, is there a reason why doesn't SQL Server support the LIMIT function or something similar? I don't want to be mean, but that really sounds like something a DBMS needs ... If it does, then I'm sorry for being so ignorant! I've been working with MySQL and SQL+ for the past 5 years so...

This question is related to sql sql-server pagination limit

The answer is


For SQL Server 2012 + you can use.

SELECT  *
FROM     sys.databases
ORDER BY name 
OFFSET  5 ROWS 
FETCH NEXT 5 ROWS ONLY 

Unfortunately, the ROW_NUMBER() is the best you can do. It's actually more correct, because the results of a limit or top clause don't really have meaning without respect to some specific order. But it's still a pain to do.

Update: Sql Server 2012 adds a limit -like feature via OFFSET and FETCH keywords. This is the ansi-standard approach, as opposed to LIMIT, which is a non-standard MySql extension.


Just for the record solution that works across most database engines though might not be the most efficient:

Select Top (ReturnCount) *
From (
    Select Top (SkipCount + ReturnCount) *
    From SourceTable
    Order By ReverseSortCondition
) ReverseSorted
Order By SortCondition

Pelase note: the last page would still contain ReturnCount rows no matter what SkipCount is. But that might be a good thing in many cases.


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

A good way is to create a procedure:

create proc pagination (@startfrom int ,@endto int) as
SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name desc) as row FROM sys.databases 
 ) a WHERE a.row > @startfrom and a.row <= @endto

just like limit 0,2 /////////////// execute pagination 0,4


From the MS SQL Server online documentation (http://technet.microsoft.com/en-us/library/ms186734.aspx ), here is their example that I have tested and works, for retrieving a specific set of rows. ROW_NUMBER requires an OVER, but you can order by whatever you like:

WITH OrderedOrders AS
(
  SELECT SalesOrderID, OrderDate,
  ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
  FROM Sales.SalesOrderHeader 
) 
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

select * from (select id,name,ROW_NUMBER() OVER (ORDER BY id  asc) as row
from tableName1) tbl1
where tbl1.row>=10 and tbl1.row<=15

Will print rows from 10 to 15.


If you are using SQL Server 2012+ vote for Martin Smith's answer and use the OFFSET and FETCH NEXT extensions to ORDER BY,

If you are unfortunate enough to be stuck with an earlier version, you could do something like this,

WITH Rows AS
(
    SELECT
              ROW_NUMBER() OVER (ORDER BY [dbo].[SomeColumn]) [Row]
            , *
        FROM
              [dbo].[SomeTable]
)
SELECT TOP 10
          *
     FROM
         Rows
    WHERE Row > 10

I believe is functionaly equivalent to

SELECT * FROM SomeTable LIMIT 10 OFFSET 10 ORDER BY SomeColumn

and the best performing way I know of doing it in TSQL, before MS SQL 2012.


If there are very many rows you may get better performance using a temp table instead of a CTE.


SELECT TOP 10 *
FROM TABLE
WHERE IDCOLUMN NOT IN (SELECT TOP 10 IDCOLUMN FROM TABLE)

Should give records 11-20. Probably not too efficient if incrementing to get further pages, and not sure how it might be affected by ordering. Might have to specify this in both WHERE statements.


 SELECT * FROM users WHERE Id Between 15 and 25

it will print from 15 to 25 as like limit in MYSQl


Use all SQL server: ;with tbl as (SELECT ROW_NUMBER() over(order by(select 1)) as RowIndex,* from table) select top 10 * from tbl where RowIndex>=10


How about this?

SET ROWCOUNT 10 

SELECT TOP 20 *
FROM sys.databases
ORDER BY database_id DESC

It gives you the last 10 rows of the first 20 rows. One drawback is that the order is reversed, but, at least it's easy to remember.


So far this format is what is working for me (not the best performance though):

SELECT TOP {desired amount of rows} * 
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY {order columns} asc)__row__ FROM {table})tmp
WHERE __row__ > {offset row count}

A note on the side, paginating over dynamic data can lead to strange/unexpected results.


as you found, this is the preferred sql server method:

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases 
 ) a WHERE a.row > 5 and a.row <= 10

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to pagination

How to use paginator from material angular? how to implement Pagination in reactJs In Flask, What is request.args and how is it used? Custom pagination view in Laravel 5 Simple pagination in javascript UITableView load more when scrolling to bottom like Facebook application How to use pagination on HTML tables? how to remove pagination in datatable Laravel Pagination links not including other GET parameters API pagination best practices

Examples related to limit

Laravel Eloquent limit and offset How to increase Neo4j's maximum file open limit (ulimit) in Ubuntu? Pagination using MySQL LIMIT, OFFSET How to fix the "508 Resource Limit is reached" error in WordPress? How to limit the number of dropzone.js files uploaded? Google drive limit number of download How can I list (ls) the 5 last modified files in a directory? Limiting Python input strings to certain characters and lengths MySQL JOIN with LIMIT 1 on joined table MySQL - UPDATE query with LIMIT