How to get N rows starting from row M from a sorted table in T-SQL

sql

pagination

database-engine

performance-optimization

byAlex Kataev·Jan 16, 2025

-- When in doubt, use a CTE!
WITH RowNumberCTE AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY SortColumn) AS RowNum
  FROM YourTable
)
SELECT *
FROM RowNumberCTE
WHERE RowNum BETWEEN M AND M + N - 1;

Promptly select N rows starting from row M by harnessing a CTE (Common Table Expression) with ROW_NUMBER() to assign row numbers in a sorted order. Deploy a WHERE clause on the computed RowNum to filter the rows. Remember to replace YourTable, SortColumn, M, and N to your matching table name, sorting column, starting row number, and number of rows you desire to pull, respectively. This method is a perfect candidate for paginated queries.

Getting the most performance with indexing

Dealing with large datasets is a common task in today’s data-loaded world. Guarantee that the SortColumn is properly indexed. This can bring an appreciable performance boost, as the database engine can promptly sort and access the required rows, avoiding unnecessary table scans.

-- Here we go! Ready to get those rows?
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY YourSortColumn) AS 'RowNum'
    FROM YourTable
) AS Sub
WHERE Sub.RowNum BETWEEN M AND (M + N - 1)

Database engines, like humans, love order and hate chaos. So, ensure your ROW_NUMBER() function corresponds to an indexed column.

Fetching rows dynamically

For times when the starting row (M) and the number of rows (N) are variables, SQL provides OFFSET and FETCH. Here’s a fun alternative way using variables with dynamic OFFSET and FETCH:

-- Variables in SQL. Who knew?
DECLARE @M INT = <<StartingRowNumber>>;
DECLARE @N INT = <<NumberOfRows>>;

-- Orders up! Here come your rows.
SELECT * 
FROM YourTable 
ORDER BY SortColumn 
OFFSET @M ROWS 
FETCH NEXT @N ROWS ONLY;

This allows dynamic pagination where M and N can be specified at runtime.

Scaling up: Approaching large datasets

For times when performance is the chief concern or when dealing with large tables like your stockpile of cat GIFs, consider alternative pagination techniques and cross-table methods that can change performance from linear to logarithmic:

A non-persistent cross-table, ordered by a clustered index.
A combination of sorting, ROW_NUMBER, and filtering to optimize the dataset being processed.
Taking advantage of server-side paging over large tables with cross-table techniques to boost efficiency.

Embracing precision with subqueries

For times when you require the maximum control over the rows chosen, enlist subqueries with NOT IN.

-- Sub-queries, the knights in shining armor!
SELECT TOP (N) *
FROM YourTable
WHERE YourPrimaryKey NOT IN (
    SELECT TOP (M-1) YourPrimaryKey
    FROM YourTable
    ORDER BY YourSortColumn
)
ORDER BY YourSortColumn;

Version compatibility

Beware, OFFSET and FETCH are available from SQL Server 2012 onwards. If you are using an older version, stick to ROW_NUMBER() and CTE or use SELECT TOP in subqueries.