在一次系统优化中，意外发现一个比较“坑”的SQL，拿出来供大家分享。
生成演示数据:
--======================================--检查测试表是否存在IF(OBJECT_ID('TB2002') IS NOT NULL)BEGIN DROP TABLE TB2002ENDGO--============================--生成测试数据并创建索引SELECT IDENTITY(INT,1,1) AS ID,* INTO TB2002FROM sys.columns CGOCREATE UNIQUE CLUSTERED INDEX IDX_ID ON TB2002( ID)GOCREATE INDEX IDX_column_id ON TB2002( column_id)
执行查询：
SELECT TOP(100) * FROM TB2002WHERE column_id=1
上面查询虽然列column_id上有索引，但由于该列的选择性不高，查询优化引擎根据预估行数生成“使用表扫描”的执行计划：
针对此测试环境，表扫描的确是最优的查询方式，但生产环境中我们经常遇到此类问题，由于统计信息或预估行数导致执行计划不优的情况，通常我们需要通过改写SQL来“让”查询优化引擎生成我们期望的查询方式，因此我将查询SQL优化为：
WITH T1 AS (SELECT TOP(100) ID AS RID FROM TB2002WHERE column_id=1)SELECT* FROM TB2002 WITH(FORCESEEK)WHERE ID IN (SELECT RID FROM T1)
查询生成的执行计划为：
查询先按照索引IDX_column_id来进行查找，再按照IDX_ID进行KEY LOOKUP，这样避免了“表扫描”操作。
当然以上都是是今天的重点，重点在于我手抖了，在优化过程中一不小心漏瞧了一个字母，于是悲剧粗线了。
漏敲一个字母的SQL为：
WITH T1 AS (SELECT TOP(100) ID AS RID FROM TB2002WHERE column_id=1)SELECT* FROM TB2002WHERE ID IN (SELECT ID FROM T1)
生成执行计划为：
先不考虑执行计划中的红叉叉，查看返回数据，我们会发现“整表的数据被返回啦”，这是什么鬼？
理论上CTE的结果集中只有RID一列，那么SELECT ID FROM T1 这个子查询应该会执行失败，我们执行以下查询
WITH T1 AS (SELECT TOP(100) ID AS RID FROM TB2002WHERE column_id=1)
执行会得到以下错误：
消息 207，级别 16，状态 1，第 5 行列名 'ID' 无效。
但对那个漏敲一个字母的SQL，查询优化引擎“赤裸裸”地忽略掉这个错误，在Nested Loops运算时只有一个“No Join Predicate”的警告，Nested Loops操作描述为“对于顶部(外部)输入的每一行，扫描底部(内部)输入，然后输出匹配的行。”，查询进行表扫描，得到一个“整表数据”的结果集T1，然后准备对结果集T1中的每一行到“CTE的结果集”中进行匹配，由于“CTE的结果集”中没有ID列，于是“莫名其妙”地认为所有行都匹配上，将整表数据都返回啦。
就好比警察抓到了一帮人，打算“在逃罪犯”系统里依次匹配每个人是不是“逃犯”，结果“在逃罪犯”系统蓝屏了，于是抓到的这帮人全成了“逃犯”，通通拉出去死啦死啦滴，还能好好玩耍了么？
--=================================================================================
群里兄弟补充,在临时表里同样有类似问题：
测试代码：
SELECT IDENTITY( INT,1,1 ) AS ID , *INTO TB2002FROM sys.columns CGOSELECT TOP 1 id AS ROWIDINTO #tmpFROM TB2002GOSELECT *FROM TB2002WHERE id IN ( SELECT ID FROM #tmp )
--=================================================================================
惨痛教训：由于优化的是DELETE 语句，本来想着通过CTE使用NOLOCK将满足条件的ID查找出来再按照ID进行删除，检查完过滤条件没有问题，直接执行导致整表数据被删除，幸好该操作没有影响业务，并有完整备份和日志备份，最终使用“指定时间点还原”+STANDBY的方式找回数据，但想想也是后怕！建议有类似习惯的童鞋在做此类操作时，先将DELETE 修改为SELECT,确保返回数据是要删除数据后，再执行DELETE。
--=================================================================================
照例是妹子镇贴和压惊
- 11楼诗意方式
- 类似的：T-SQL查询：慎用 IN 和 NOT IN，http://blog.csdn.net/kk185800961/article/details/41764803
- 10楼剑走江湖
- 在CTE中的SELECT子句中的字段列重新使用了别名，那么也就可以理解为该CTE的字段列集应该以最新的为主的，在使用中也要使用最新列的，使用SQL Prompt工具针对CTE的列出的字段只有最新的字段名，如下图：，，，针对这个问题，我倒是认为一定要认真的，使用的字段必须在使用的“表”（物理表、CTE、表表达式、内连表函数和表值函数等等）中。下图我使用的测试的T-SQL代码如下：，，测试也显示SQL Server语法检查有时候确实执行成功的却没有检查出使用的字段列不在使用的表中“表”中。，，建议：使用字段列时通过表名或其表名限制引用，这样就可以。避免因粗心导致的以上的问题。
- 9楼桦仔
- 加个begin tran
- 8楼aiyouheiya
- 这坑真心得注意...以前看到过带红叉的 没注意过..想想也后怕了
- 7楼Lvanhades666
- 这种事情 我在MySQL里也碰到了 结果导致我把整个表全部删除了。幸好有隔天的备份。跟这个情况很类似
- 6楼剑走江湖
- 也是第一次遇到，明天继续好好测试和研读博主的文章，谢谢博主分享。
- 5楼潇湘隐者
- 这个分享有意思，第一次见这种案例！顶起！
- 4楼wy123
- 这种写法用在删除操作产生的后果确实很恐怖，以后用in的时候要格外慎重了
- 3楼不夜橙
- 注意用IN的时候别引用到外表的字段
- 2楼害怕飞的鸟
- 坑
- 1楼dbanote
- 08 12 会报错 14 会出现红叉，作为dba任何操作还是要谨慎的