15

I'm looking to some expresion like this (using SQL Server 2008)

SELECT TOP 10 columName FROM tableName

But instead of that I need the values between 10 and 20. And I wonder if there is a way of doing it using only one SELECT statement.

For example this is useless:

SELECT columName FROM
(SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName) AS alias
WHERE RowNum BETWEEN 10 AND 20

Because the select inside brackets is already returning all the results, and I'm looking to avoid that, due to performance.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user1823901
  • 151
  • 1
  • 1
  • 4

5 Answers5

10

Use SQL Server 2012 to fetch/skip! 

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

There's nothing better than you're describing for older versions of sql server. Maybe use CTE, but unlikely to make a difference. 

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

or, you can remove top 10 rows and then get next 10 rows, but I double anyone would want to do that.

RAS
  • 3,375
  • 15
  • 24
  • 1
    Upgrading to SQL Server 2012 is usually not a "snap your fingers" option. And I'd argue there *are* better answers in older versions of SQL Server. For example, why not retrieve just the *key* values when applying row numbers, then join that output (which will be *much* skinner) with the table? – Aaron Bertrand Nov 16 '12 at 18:58
  • @AaronBertrand, you're right. I completely agree that upgrading is not easy, but in case OP is using this statement a lot and really cares about performance, this is something he/she can take into consideration. – RAS Nov 16 '12 at 19:01
  • in 2019, this is the best answer – MisterEd Nov 15 '19 at 13:35
10

There is a trick with row_number that does not involve sorting all the rows.

Try this:

SELECT columName
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (select NULL as noorder)) AS RowNum, *
      FROM tableName
     ) as alias
WHERE RowNum BETWEEN 10 AND 20

You cannot use a constant in the order by. However, you can use an expression that evaluates to a constant. SQL Server recognizes this and just returns the rows as encountered, properly enumerated.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm not sure how this can help. Won't it be the case that if ROW_NUMBER() is applied without explicit sorting, the paging will be useless (presented in arbitrary order)? – Aaron Bertrand Nov 16 '12 at 19:01
  • I've found that using this construct, the row numbers are assigned as the rows are generated. Or, more specifically, there seems to be no intermediate sorting of the table (at least in terms of performance). I typically use this to assign numeric ids to rows when using `SELECT INTO`. And, yes, the row numbers are arbitrary, but the queries in the question did not have `order by` clauses. – Gordon Linoff Nov 16 '12 at 21:00
  • But again, observation <> guarantee. I am sure presentation order is important, but it's usually left out of these questions for brevity - not because they want to encourage answers that could potentially provide arbitrary results. – Aaron Bertrand Nov 16 '12 at 21:28
  • Also if `someId` is indexed it is doubtful that generating row numbers against a constant will provide any real performance benefit. – Aaron Bertrand Nov 16 '12 at 21:30
3

Why do you think SQL Server would evaluate the entire inner query? Assuming your sort column is indexed, it'll just read the first 20 values. If you're really nervous you could do this:

Select
  Id 
From (
  Select Top 20 -- note top 20
    Row_Number() Over(Order By Id) As RowNum,
    Id 
  From
    dbo.Test
  Order By
    Id
  ) As alias
Where
  RowNum Between 10 And 20
Order By
  Id

but I'm pretty sure the query plan is the same either way.

(Really) Fixed as per Aaron's comment.

http://sqlfiddle.com/#!3/db162/6

Laurence
  • 10,896
  • 1
  • 25
  • 34
  • Technically you'd want an ORDER BY in there. The inner select is not *guaranteed* to return rows in the same order they are applied by ROW_NUMBER(), even though that's what you might observe in most cases. Also note that the inner query gets more and more expensive as you move through the 2nd, 3rd, ... 500th page. Ideally a solution would provide linear performance. – Aaron Bertrand Nov 16 '12 at 19:09
  • You'd need something like skip lists to do that in a stateless way. I don't think SQL Server has an index structure that provides constant performance to move to the nth location in an index. You could do better if the underlying set was unchanging, you could then precompute page offsets and store them in a separate table. Thanks for the fix re ordering the outer query. – Laurence Nov 16 '12 at 19:23
  • The inner query actually is the one that needs an order (the outer does for presentation purposes, but the inner needs one to ensure the correct row selection). Also I wasn't suggesting anything about providing stateless... users have come to expect that the underlying data can change while scrolling. But seeking to the nth page *can* be relatively linear in SQL Server, but not if you do so by `SELECT TOP 10 FROM (SELECT TOP (page*pagesize) FROM big table)` – Aaron Bertrand Nov 16 '12 at 19:29
  • Thanks again. Does that mean the query without `top` is broken? SQL Server won't let you put an order by on this. Kind of odd for what's considered a standard solution, but I think you're right. – Laurence Nov 16 '12 at 19:43
  • What does "SQL Server won't let you put an order by on this" mean? What is "on this" and "won't let"? – Aaron Bertrand Nov 16 '12 at 19:48
  • See the second query http://sqlfiddle.com/#!3/db162/13. Without `top`, you can't use `order by` in the inner query. – Laurence Nov 16 '12 at 19:59
  • Understood, your solution uses TOP, so I was not advocating removing TOP. But your solution will also become less efficient as that inner TOP gets larger. – Aaron Bertrand Nov 16 '12 at 20:12
1

One more option

SELECT TOP(11) columName
FROM dbo.tableName
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY someId) BETWEEN 10 AND 20 
     THEN ROW_NUMBER() OVER (ORDER BY someId) ELSE NULL END DESC
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • This is great! This version is independent of the selected range. Other versions can take notably longer if you select higher ranges (5,000-5,050) compared to lower ranges (0-50) – king_nak Mar 16 '18 at 11:40
0

You could create a temp table that is ordered the way you want like:

SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName into ##tempTable ...

That way you have an ordered list of rows. and can just query by row number the subsequent times instead of doing the inner query multiple times.

user3688672
  • 1