Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?
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.