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

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).

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.

StackExchange Database Administrators Q#27467, answer score: 23