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

Question

There is a simple way to get top N rows from any table:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn 

Is there any efficient way to query M rows starting from row N

For example,

Id Value 1    a 2    b 3    c 4    d 5    e 6    f 

And query like this

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */ 

queries 2 rows starting from 3d row, i.e 3d and 4th rows are returned.

Answer 1

UPDATE If you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query

I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):

WITH NumberedMyTable AS (     SELECT         Id,         Value,         ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber     FROM         MyTable ) SELECT     Id,     Value FROM     NumberedMyTable WHERE     RowNumber BETWEEN @From AND @To 

Answer 2

The problem with the suggestions in this thread and elsewhere on the web is that all the proposed solutions run in linear time with respect to the number of records. For example, consider a query like the following.

select * from (     select         Row_Number() over (order by ClusteredIndexField) as RowNumber,         *     from MyTable ) as PagedTable where RowNumber between @LowestRowNumber and @HighestRowNumber; 

When getting page 1, the query takes 0.577 seconds. However, when getting page 15,619, this same query takes over 2 minutes and 55 seconds.

We can greatly improve this by creating a record number, index cross-table as shown in the following query. The cross-table is called PagedTable and is non-persistent.

select * from (     select         Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,         ClusteredIndexField     from MyTable ) as PagedTable left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField where RowNumber between @LowestRowNumber and @HighestRowNumber; 

Like in the previous example, I tested this on a very wide table with 780,928 records. I used a page size of 50, which resulted in 15,619 pages.

The total time taken for page 1 (the first page) is 0.413 seconds. The total time taken for page 15,619 (the last page) is 0.987 seconds, merely twice times as long as page 1. These times were measured using SQL Server Profiler and the DBMS was SQL Server 2008 R2.

This solution works for any case when you are sorting your table by an index. The index does not have to be clustered or simple. In my case, the index was composed of three fields: varchar(50) asc, varchar(15) asc, numeric(19,0) asc. That the performance was excellent despite the cumbersome index just further demonstrates that this approach works.

However, it is critical that the order by clause in the Row_Number windowing function corresponds to an index. Otherwise performance will degrade to the same level as the first example.

This approach does still require a linear operation to generate the non-persistent cross-table, but since that's just an index with a row number added, it happens very quickly. In my case it took 0.347 seconds, but my case had varchars that needed to be copied. A single numeric index would take far less time.

For all practical purposes, this design reduces the scaling of server-side paging from a linear operation to a logarithmic operation allowing the scaling of large tables. Below is the complete solution.

-- For a sproc, make these your input parameters declare     @PageSize int = 50,     @Page int = 15619;  -- For a sproc, make these your output parameters declare @RecordCount int = (select count(*) from MyTable); declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize); declare @Offset int = (@Page - 1) * @PageSize; declare @LowestRowNumber int = @Offset; declare @HighestRowNumber int = @Offset + @PageSize - 1;  select     @RecordCount as RecordCount,     @PageCount as PageCount,     @Offset as Offset,     @LowestRowNumber as LowestRowNumber,     @HighestRowNumber as HighestRowNumber;  select * from (     select         Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,         ClusteredIndexField     from MyTable ) as PagedTable left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField where RowNumber between @LowestRowNumber and @HighestRowNumber;