LIMIT 10..20 in SQL Server

sql sql-server pagination limit
marcgg picture marcgg · Jun 9, 2009 · Viewed 308.1k times · Source

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

Answer

Martin Smith picture Martin Smith · Feb 11, 2012

For SQL Server 2012 + you can use.

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

Related questions

Equivalent of LIMIT and OFFSET for SQL Server?

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?

Read More
What is the best way to paginate results in SQL Server

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?

Read More
Implement paging (skip / take) functionality with this query

I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one. I have the following query, which works perfectly. But I would like to implement paging with …

Read More