使用CTAS创建Azure SQL数据仓库临时表会导致行的意外顺序

Question

我已经编写了一个带有注释的代码示例，所以我将让它来解释：

/*
    Querying sys.system_views to produce a set of rows to use as an example.
    No matter how many times the SELECT statement is ran, the results are always the same.
*/
SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS sequence,
    s.name + '.' + sv.name as name
FROM sys.system_views sv
INNER JOIN sys.schemas s ON s.schema_id = sv.schema_id
WHERE s.name = 'INFORMATION_SCHEMA'

/*
    Creating a temporary table using the CTAS principle, as is documented at:
    https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-loops
    (Since this behaviour was noticed when trying to execute procedures in a defined order.)
*/
CREATE TABLE #list WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS sequence,
    s.name + '.' + sv.name as name
FROM sys.system_views sv
INNER JOIN sys.schemas s ON s.schema_id = sv.schema_id
WHERE s.name = 'INFORMATION_SCHEMA'

/*
    The results in the temporary table #list are not the
    same as the results of the SELECT statement when ran independently.

    No matter how many times the temporary table is created,
    the results are in the same order, which again,
    is not the resulting order when running the SELECT statement.
*/

SELECT *
FROM #list;

DROP TABLE #list;

下面是并行查询结果的示例图片.

从#list查询SELECT和SELECT结果

通过在ROW_NUMBER函数中使用适当的顺序可以很容易地防止这个问题：

CREATE TABLE #list WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
    ROW_NUMBER() OVER(ORDER BY s.name + '.' + sv.name) AS sequence,
    s.name + '.' + sv.name as name
FROM sys.system_views sv
INNER JOIN sys.schemas s ON s.schema_id = sv.schema_id
WHERE s.name = 'INFORMATION_SCHEMA'

所以我的问题是，为什么#list临时表中的顺序与SELECT语句不同？

Answer 1

如果使用非确定性SQL结构，则不能期望确定性结果。ROW_NUMBER() OVER(ORDER (SELECT NULL))并不保证任何特定的排序，因为所有行都绑定在排序值上。任何订单都是正确的，您将得到最方便的执行计划。

-马丁·史密斯

在问题的评论中回答和讨论。