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

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 1

For SQL Server 2012 + version, you can use the below code:

Refer to this video to learn ORDER BY in detail:

Query:

SELECT  *

FROM     sys.databases

ORDER BY name 

OFFSET  5 ROWS 

FETCH NEXT 5 ROWS ONLY 

For more information you can refer to this : 

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms188385(v=sql.110)