LIMIT 10..20 in SQL Server
For SQL Server 2012 + you can use.
SELECT *FROM sys.databasesORDER 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:
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