计算派生表的 SQL Server ROW_NUMBER() OVER() [英] Calculating SQL Server ROW_NUMBER() OVER() for a derived table

问题描述

在某些其他数据库(例如 DB2 或带有 ROWNUM 的 Oracle)中，我可以省略排名函数的 OVER() 中的 ORDER BY 子句代码> 子句.例如:

In some other databases (e.g. DB2, or Oracle with ROWNUM), I can omit the ORDER BY clause in a ranking function's OVER() clause. For instance:

ROW_NUMBER() OVER()

这在与有序派生表一起使用时特别有用，例如:

This is particularly useful when used with ordered derived tables, such as:

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

如何在 SQL Server 中进行模拟?我发现有人在使用 this trick，但这是错误的，因为它会根据派生表的顺序表现出不确定性:

How can this be emulated in SQL Server? I've found people using this trick, but that's wrong, as it will behave non-deterministically with respect to the order from the derived table:

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

一个具体的例子(可以在 SQLFiddle 上看到):

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

此外，在我的情况下，我无法重用派生表中的任何表达式来重现 ORDER BY 子句，因为派生表可能不可用，因为它可能由某些外部逻辑提供.

Also, I cannot reuse any expression from the derived table to reproduce the ORDER BY clause in my case, as the derived table might not be available as it may be provided by some external logic.

那我该怎么办呢?我能做到吗?

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

推荐答案

Row_Number() OVER (ORDER BY (SELECT 1)) 技巧应该NOT被视为一种避免改变底层数据顺序的方法.这只是一种避免导致服务器执行额外的和不需要的排序的方法(它可能仍会执行排序，但与按列排序相比，它会花费尽可能少的成本).

The Row_Number() OVER (ORDER BY (SELECT 1)) trick should NOT be seen as a way to avoid changing the order of underlying data. It is only a means to avoid causing the server to perform an additional and unneeded sort (it may still perform the sort but it's going to cost the minimum amount possible when compared to sorting by a column).

SQL Server 中的所有查询绝对必须在最外层查询中有一个 ORDER BY 子句，以便以有保证的方式对结果进行可靠排序.

All queries in SQL server ABSOLUTELY MUST have an ORDER BY clause in the outermost query for the results to be reliably ordered in a guaranteed way.

关系数据库中不存在保留原始顺序"的概念.表和查询必须始终被视为无序，除非在最外层查询中指定了 ORDER BY 子句.

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

您可以尝试 100,000 次相同的无序查询，并且始终以相同的顺序接收它，因此您可以相信您可以依赖所述顺序.但这将是一个错误，因为有一天，某些事情会发生变化，并且不会按照您期望的顺序进行.一个例子是当数据库升级到新版本的 SQL Server 时——这会导致许多查询更改其顺序.但它不必有那么大的变化.添加或删除索引之类的小事都会导致差异.还有更多:安装服务包.对表进行分区.创建包含相关表的索引视图.到达某个临界点，选择扫描而不是搜索.等等.

You could try the same unordered query 100,000 times and always receive it with the same ordering, and thus come to believe you can rely on said ordering. But that would be a mistake, because one day, something will change and it will not have the order you expect. One example is when a database is upgraded to a new version of SQL Server--this has caused many a query to change its ordering. But it doesn't have to be that big a change. Something as little as adding or removing an index can cause differences. And more: Installing a service pack. Partitioning a table. Creating an indexed view that includes the table in question. Reaching some tipping point where a scan is chosen instead of a seek. And so on.

除非您说服务器，ORDER BY"，否则不要依赖结果进行排序.

Do not rely on results to be ordered unless you have said "Server, ORDER BY".

这篇关于计算派生表的 SQL Server ROW_NUMBER() OVER()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！