1

I know that you can use ROW_NUMER() to get the row number and then perform WHERE on on the results, as shown here:

USE AdventureWorks2012;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader 
) 
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

However, this will first sort all the data and will only then extract the row range.

Since one can find the kth member of a sorted array: How to find the kth largest element in an unsorted array of length n in O(n)?, I would hope it would also be possible in SQL.

Community
  • 1
  • 1
Mugen
  • 8,301
  • 10
  • 62
  • 140
  • it is in SQL Server 2012+ – Lamak Feb 10 '16 at 12:13
  • @Lamak What do you mean? it is if I use the above syntax? what if I do a nested `SELECT` instead of `WITH`? – Mugen Feb 10 '16 at 12:14
  • 1
    https://www.mssqltips.com/sqlservertip/2420/sql-server-2012-server-side-paging/ – mohan111 Feb 10 '16 at 12:15
  • I don't think this is possible in SQL Server (any version) or SQL in general. You can use `FETCH` along with `ORDER BY` in the outer query, which allows paging (the the use of an index). But I don't think the algorithm you are referring to is implemented in any database. – Gordon Linoff Feb 10 '16 at 12:16
  • 1
    No, you can use [OFFSET](https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396) – Lamak Feb 10 '16 at 12:17
  • `OFFSET` seems perfect. Too bad I need to support pre-2012 :( – Mugen Feb 10 '16 at 13:06

1 Answers1

0

The sort can be avoided with an index on OrderDate:

CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate);

An ordered scan of the this index will be performed until the specified upper ROW_NUMBER() limit is reached, limiting the scan to that number of rows. ROW_NUMBER() values lower than the specified range will be discarded from the results.

As with any set-based pagination technique in SQL where a useful ordering index exists, performance will largely depend on the number of rows that need to be skipped and returned.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Well, actually in my case the order by is dynamic and could also contain multiple columns or aggregation values on which I would like to order – Mugen Feb 10 '16 at 12:29
  • @Mugen, indexes are required to avoid sorting for the `ROW_NUMBER()`. In the case of computed values, a sort will be required. – Dan Guzman Feb 10 '16 at 13:06