SQLSERVER中CTE（WITH AS）语句的优化器行为分析

momo3142016-01-30相同方式共享非商业用途署名转载

话说，今天遇到一个问题，是这样的：

SQLSERVER中有一张表，其中一个备注字段[MEMO]，这个字段呢，是字符串类型的，但是其中也会存一些Id之类的数字类型的值。现在呢，需要把则个字段中数值类型的值小于 2 的查出来，怎么办呢？

OK，看起来很简单，我们来模拟一下，假设下面这张 @SOURCE 表就是我们的数据源

DECLARE @SOURCE TABLE
(
    [Id] INT IDENTITY(1, 1),
    [MEMO] NVARCHAR(5)
)

INSERT INTO @SOURCE
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D' UNION
SELECT '1' UNION
SELECT '2' UNION
SELECT '3' UNION
SELECT '4'

SELECT * FROM @SOURCE

谁都知道直接写 SELECT * FROM @SOURCE WHERE CAST([MEMO] AS INT) < 2 肯定会报错，因为我们没办法把字母转换为数字。

那么，首先，我们肯定要把[MEMO]字段为数值类型的数据单独搞出来，然后再对其进行查询应该就OK了吧？大致有下面几种写法：

方案一：

SELECT * 
FROM
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
) T
WHERE CAST([MEMO] AS INT) < 2

这种写法必然是不行哒(●′ω`●)，因为显然优化器会自动将语句优化为直接查询而非子查询，那么也就是说，我们必须要保证语句在执行的时候是强制执行子查询的，于是就有了方案二三四。

方案二/方案三

--方案二/三分别使用 临时表#TEMPSOURCE 和 表变量@TEMPSOURCE 的方式
SELECT * INTO #TEMPSOURCE FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
SELECT * FROM #TEMPSOURCE WHERE CAST([MEMO] AS INT) < 2
DROP TABLE #TEMPSOURCE

毫无疑问，可以正常执行。因为加入了临时表，导致了第一步子查询结束后的INSERT操作，这个操作是不会优化掉的，所以可以保证第二步的查询中的[MEMO]一定是数值类型。

方案四

WITH TEMPSOURCE 
AS
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
)
SELECT * FROM TEMPSOURCE WHERE [MEMO] < 2

好啦，神奇的方案四出场啦！虽然看起来跟方案二/方案三差不多，那么为什么要单独列出来呢？这个方案到底行不行呢？

OK，不卖关子，神奇海螺非常确定的告诉你：这种写法是不行哒！

在将 nvarchar 值 'A' 转换成数据类型 int 时失败。

如果你也觉得应该可以的话，那么请继续往下看：

为什么会报错呢？我明明把符合要求的数据放到表TEMPSOURCE 中去了啊？

我们来看一下方案四的执行计划： 因为实际在执行语句的过程中会报错，所以我们只能查看预估的执行计划，而非实际执行计划 CTE语句的预估执行计划直接表扫描得到结果！！！ Inconceivable！！！

那么，我们再来看看方案二和方案三的执行计划是什么样子的呢：内存表语句的执行计划

第一步插入内存表；第二步表扫描得到结果。

这才对嘛！这才是我们希望的流程呀！

那么到底为什么会导致这种差异呢？

WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。

那么重点来了，看执行计划，我们确实只对其进行了一次查询，所以根本就没有被塞到临时表里吗？

好，既然如此，那么我们就执行一个多次查询试一下：

WITH TEMPSOURCE 
AS
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
)
SELECT *
FROM
TEMPSOURCE A
INNER JOIN 
TEMPSOURCE B
ON CAST(A.[MEMO] AS INT) = B.[Id]
--WHERE CAST(B.[MEMO] AS INT) > 2

CTE语句多次查询的执行计划

果然！可以正常执行！

结论：如果 WITH AS 短语所定义的表名被调用两次以上，则优化器会自动将WITH AS 短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。

疑问：大家可能注意到了，在上面最后的一条语句中，我把 WHERE查询条件给注释掉了。为什么呢？

因为我发现加上 WHERE条件之后，执行又会报错，而且我查看了他们的执行计划，是一模一样的，是不是很奇怪？
如果将上面的 WHERE条件修改为 WHERE B.[Id] > 2 ，也就是说不再涉及类型转换的话，也是可以正常执行的。

以上。百思不得其解("▔□▔)/

如果各位大侠知道原因的话，麻烦评论里写一下，共同进步 (•̀ᴗ•́)و ̑̑