sql - 投影内相关子查询排序的影响

Question

我注意到 SQL Server(在本例中为 SQL Server 2008)处理 select 语句中的相关子查询的方式有点出乎意料。我的假设是查询计划不应仅受子查询(或列，就此而言)在 select 语句的投影子句中的写入顺序的影响。然而，情况似乎并非如此。

考虑以下两个查询，除了 CTE 中子查询的顺序外，它们是相同的:

--query 1: subquery for Color is second
WITH vw AS
(
 SELECT p.[ID],
  (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName],
  (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color]
 FROM Person p
)
SELECT ID, Color, FirstName
FROM vw
WHERE Color = 'Gray';


--query 2: subquery for Color is first
WITH vw AS
(
 SELECT p.[ID],
  (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color],
  (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName]
 FROM Person p
)
SELECT ID, Color, FirstName
FROM vw
WHERE Color = 'Gray';

如果查看这两个查询计划，您会发现每个子查询都使用了一个外连接，并且连接的顺序与子查询的写入顺序相同。有一个过滤器应用于颜色的外部连接结果，以过滤掉颜色不是“灰色”的行。 (我觉得 SQL 会对颜色子查询使用外部连接很奇怪，因为我对颜色子查询的结果有一个非空约束，但是没关系。)

大部分行都被滤色器移除了。结果是查询 2 比查询 1 便宜得多，因为第二个连接涉及的行数更少。抛开构建这样一个声明的所有原因，这是预期的行为吗？ SQL Server 不应该选择在查询计划中尽早移动过滤器，而不考虑子查询的编写顺序吗？

编辑: 澄清一下，我探索这种情况是有正当理由的。我可能需要创建一个包含类似构造的子查询的 View ，现在很明显，基于从 View 投影的这些列的任何过滤都会因为列的顺序而在性能上有所不同!

Answer 1

随着 TOP 运算符在这里发挥作用，查询优化器对统计数据明显视而不见，因此它会寻找其他线索以了解如何最好地使用它，例如首先实例化 CTE 的相关部分。

而且它是一个外部连接，因为如果没有返回，子查询将被用作 NULL，并且系统首先实例化它。如果您使用聚合而不是 TOP，您可能会得到一个略有不同但更一致的计划。