LIMIT 10 20 in SQL Server

Question

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

Is there another way for me to do that ?

Answer 1

The LIMIT clause is not included in traditional SQL. It is supported by MySQL, PostgreSQL, and SQLite as a vendor extension to SQL. Similar capabilities in other database brands (such as TOP in Microsoft SQL Server) may exist, although they don't always function in the same way. In Microsoft SQL Server, using TOP to simulate the LIMIT clause is challenging. In some circumstances, it just doesn't work.

Microsoft SQL Server 2005 and later support the approach you demonstrated, using ROW NUMBER(). The best answer that just applies to the query is this one, for the time being. The first count + offset rows can also be fetched using TOP, and the first offset rows can then be sought past using the API.

I hope this helps you.