Computing SQL Server ROW_NUMBER () OVER () for a view

Question

In some other databases (such as DB2 or Oracle with ROWNUM ), I can omit the ORDER BY in the OVER() ranking article. For example:

 ROW_NUMBER() OVER() 

This is especially useful when used with ordered views, for example:

 SELECT t.*, ROW_NUMBER() OVER() FROM ( SELECT ... ORDER BY ) t 

How can this be emulated in SQL Server? I found people using this trick , but this is wrong, as it will behave non-deterministically with respect to the order from the view:

 -- This order here ---------------------vvvvvvvv SELECT t.*, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM ( SELECT TOP 100 PERCENT ... -- vvvvv ----redefines this order here ORDER BY ) t 

Concrete example (as can be seen on SQLFiddle ):

 SELECT v, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN FROM ( SELECT TOP 100 PERCENT 1 UNION ALL SELECT TOP 100 PERCENT 2 UNION ALL SELECT TOP 100 PERCENT 3 UNION ALL SELECT TOP 100 PERCENT 4 -- This descending order is not maintained in the outer query ORDER BY 1 DESC ) t(v) 

Also, I cannot reuse any expression from the view to reproduce the ORDER BY in my case, since the view may not be available because it can be provided by some external logic.

So how can I do this? Can I do this at all?

Answer 1

The triple Row_Number() OVER (ORDER BY (SELECT 1)) should NOT be considered as a way to avoid changing the order of the underlying data. This is just a way to prevent the server from performing additional and unnecessary sorting (it can still sort, but it will cost the smallest possible amount compared to sorting by column).

All queries on the SQL server ABSOLUTELY MUST have an ORDER BY in the outermost query so that the results are reliably ordered in a guaranteed way.

The concept of "preserving the original order" does not exist in relational databases. Tables and queries should always be considered unordered until the ORDER BY is specified in the outermost query.

You can try the same unordered request 100,000 times and always receive it with the same order, and thus believe that you can rely on the specified ordering. But that would be a mistake, because one day something will change and he will not have the expected order. For example, when a database is upgraded to a new version of SQL Server, it triggered a lot of queries to reorder them. But that should not be a big change. Something like adding or removing an index can lead to differences. And one more thing: installing the service pack. Table partitioning. Create an indexed view that includes the corresponding table. Reaching a certain survey point when scanning is selected instead of searching. And so on.

Do not rely on the results you want to order if you did not say "Server, ORDER BY ".