SQL Server 2005 中 CTE 的评估

Question

我有一个关于 MS SQL 如何评估 CTE 内的函数的问题。几次搜索没有找到与此问题相关的任何结果，但如果这是常识并且我只是落后于潮流，我深表歉意。这不是第一次:-)

这个查询是我实际正在做的事情的简化（并且显然不太动态）版本，但它确实展示了我遇到的问题。它看起来像这样：

CREATE TABLE #EmployeePool(EmployeeID int, EmployeeRank int);

INSERT INTO #EmployeePool(EmployeeID, EmployeeRank)
  SELECT 42, 1
  UNION ALL 
  SELECT 43, 2;

DECLARE @NumEmployees int;
SELECT @NumEmployees  = COUNT(*) FROM #EmployeePool;

WITH RandomizedCustomers AS (
  SELECT CAST(c.Criteria AS int) AS CustomerID,
         dbo.fnUtil_Random(@NumEmployees) AS RandomRank
    FROM dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') c)
SELECT rc.CustomerID,
       ep.EmployeeID
  FROM RandomizedCustomers rc
  JOIN #EmployeePool ep ON ep.EmployeeRank = rc.RandomRank;

DROP TABLE #EmployeePool;

对于上述的所有执行，可以假设以下情况：

• dbo.fnUtil_Random() 的结果始终是一个大于零且小于或等于的 int 值传入的参数。由于上面使用值为 2 的 @NumEmployees 调用该函数，因此该函数的计算结果始终为 1 或 2。

• dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') 的结果生成一个单列单行表，其中包含基本类型为 'int' 的 sql_variant，该表具有值 219935。

鉴于上述假设，上面表达式的结果应该始终生成一个包含一条记录（CustomerID 和 EmployeeID）的两列表格（无论如何，对我来说）是有道理的。 CustomerID 应始终为 int 值 219935，EmployeeID 应为 42 或 43。

但是，情况并非总是如此。有时我会得到预期的单曲记录。有时我会得到两条记录（每个 EmployeeID 一条），还有一些时候我没有得到任何记录。但是，如果我用真正的临时表替换 RandomizedCustomers CTE，问题就会完全消失。

每当我认为我对这种行为有一个解释时，结果证明它没有意义或不可能，所以我实际上无法解释为什么会发生这种情况。由于当我用临时表替换 CTE 时不会发生问题，因此我只能假设它与在连接到该 CTE 期间评估 CTE 内的函数有关。你们有什么理论吗？

Answer 1

SQL Server 的优化器可以自由决定是否重新评估CTE。

例如，此查询：

WITH    q AS
        (
        SELECT  NEWID() AS n
        )
SELECT  *
FROM    q
UNION ALL
SELECT  *
FROM    q

将生成两个不同的 NEWID()，但是，如果您使用缓存的 XML 计划将 CTE 包装到Eager Spool 操作，记录将是相同的。