Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

Question

E.g. consider the SQL query:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
   [FooTable] AS A

Here I observe the results being returned sorted by A.[Name]. If I change the sort column defined in the ROW_NUMBER function to another column then again the results become sorted by that column.

I was expecting the row number to be assigned to the rows but I was not expecting the rows to come back sorted by that same criteria. Is this simply a side effect of how the query is being executed (in my case on SQL Server 2008 R2) or is this behaviour guaranteed? (I could find no reference to such a guarantee).

Answer 1

If you had asked the question I think you actually meant to ask:

How can I order by ROW_NUMBER() without repeating the complex ORDER BY expression?

We could have told you to create an alias for the ROW_NUMBER() expression, and then sort using the alias:

SELECT
   A.[Name],
   rn = ROW_NUMBER() OVER (ORDER BY <complex expression>)
FROM
   dbo.[FooTable] AS A
ORDER BY rn;

Now you only have to change the expression in one place, and the resulting sort will be based on that expression without having to repeat it.

Answer 2

Absolutely not. Proof:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
   ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
   [FooTable] AS A

The only way to guarantee an order in SQL is to ask for it, use ORDER BY on the result itself.

Answer 3

Actually the (old) question could be extended to also include the partition by part, as there seems to be an implicit order by first the partition by and then the order by part(s)

So it is not quite as simple as some has suggested as to assign the row_number and use that for ordering.

We would need a nested sql extracting the partition by clause as well (if we do not want to simply repeat it of course)

Empirically we seem to get the last line of ordering implicitly

Select
  <field-list>, 
  rn=Row_Number() over(partition by <PartionClause>) order by <OrderClause>
from ....
Order by <PartionClause> asc, rn asc

But what we lack is any proof or guarantee that it always holds.

(If there are multiple Row_Number() calls in play, it is the LAST one that seems to give the ordering)

ALSO NOTE if you explicitly add the order by the execution plan clearly shows a final sort-step, and sorting an already sorted set is kind of worst-case, hence it takes substantially longer with the Order By than without