Equivalent of LIMIT and OFFSET for SQL Server?

sql sql-server pagination limit offset
Earlz picture Earlz · Jan 25, 2010 · Viewed 244.2k times · Source

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?

Answer

Somnath Muluk picture Somnath Muluk · Feb 13, 2012

This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards.

Limit with offset to select 11 to 20 rows in SQL Server:

SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
  • ORDER BY: required
  • OFFSET: optional number of skipped rows
  • NEXT: required number of next rows

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql

Related questions

LIMIT 10..20 in SQL Server

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 …

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