LIMIT 10..20 in SQL Server
For SQL Server 2012 + you can use.
SELECT *
FROM sys.databases
ORDER BY name
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY
The LIMIT
clause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite.
Other brands of database may have similar features (e.g. TOP
in Microsoft SQL Server), but these don't always work identically.
It's hard to use TOP
in Microsoft SQL Server to mimic the LIMIT
clause. There are cases where it just doesn't work.
The solution you showed, using ROW_NUMBER()
is available in Microsoft SQL Server 2005 and later. This is the best solution (for now) that works solely as part of the query.
Another solution is to use TOP
to fetch the first count + offset rows, and then use the API to seek past the first offset rows.
See also:
- "Emulate MySQL LIMIT clause in Microsoft SQL Server 2000"
- "Paging of Large Resultsets in ASP.NET"