How to implement LIMIT with SQL Server?

Working with different server versions

ROW_NUMBER() in SQL Server pre-2012

Before SQL Server 2012, ROW_NUMBER() was the James Bond of row limitation. Paired with CTE or a subquery, it made limited selections possible.

WITH NumberedMyTable AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNum  --"RowNum" sounds like a cool superpower, doesn't it?
  FROM YourTable
)
SELECT * FROM NumberedMyTable WHERE RowNum BETWEEN 11 AND 20;


OFFSET FETCH in SQL Server 2012 and onwards

In SQL Server 2012, the OFFSET FETCH clause was introduced. Apart from being intuitive, it also saved you from writing the Great American Novel of SQL queries.

SELECT * FROM YourTable
ORDER BY YourColumn  --Sort before you set off!
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;


Remember, OFFSET FETCH only works when paired with an ORDER BY clause.

Optimizing performance

Indexed for speed

By using indexes matching your ORDER BY clause, you can significantly boost speed when using ROW_NUMBER() or OFFSET FETCH.

Size does matter

Instead of SELECT *, specify the columns you want. This shrinks result set sizes, which is key for high query performance.

Uniqueness is a virtue

A non-unique ORDER BY clause can lead to unpredictable pagination results. We're coding, not playing Russian roulette! Include a unique column or a combo of columns in your ORDER BY.

Retrieving specific row ranges

A quick stroll between rows with ROW_NUMBER()

Utilize ROW_NUMBER() with a BETWEEN clause if you need to cherry-pick rows from a specific range.

WITH NumberedMyTable AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNum
  FROM YourTable
)
SELECT * FROM NumberedMyTable WHERE RowNum BETWEEN 101 AND 110;  --Only the worthy shall pass!


OFFSET FETCH for specific ranges

From SQL Server 2012 onward, OFFSET FETCH gives you an out-of-the-box solution for selecting specific row ranges.

SELECT * FROM YourTable
ORDER BY YourColumn
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;  --"Skip a hundred, take ten!" Sounds like a Black Friday sale, doesn't it?


Utilizing CTEs for complex queries

Ensure your complex queries are both organized and readable by using CTEs.

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNum  --Our familiar friend "RowNum" is back!
  FROM YourTable WHERE YourColumn > 100
)
SELECT * FROM CTE WHERE RowNum BETWEEN 21 AND 30;  --Catch 10 rows, if you can!


By arranging your subqueries with CTEs, you'll scale both the performance and maintainability mountains with ease.

Migrate from different SQL dialects

Switching from MySQL? Below is how you adapt MySQL’s LIMIT to SQL Server’s OFFSET FETCH.

MySQL's LIMIT:

SELECT * FROM YourTable LIMIT 10, 20;  --"LIMIT 10,20", sounds like a well-balanced diet!


SQL Server's OFFSET FETCH:

SELECT * FROM YourTable
ORDER BY YourColumn
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;


Transition between different SQL dialects with ease!