T-SQL之变量导致索引无效

(一)问题提出

1，在开发中是否遇到一个情况，就是在where后写明具体值时可以用到索引，使用变量时却不行了呢？

2，是否开始怀疑MS SQL 出现了编译问题。

（二）测试过程

1，建立测试数据

?

CREATE TABLE t_order ( orderid INT IDENTITY ( 1 , 1 ) PRIMARY KEY , ordertime DATETIME, productname VARCHAR (50)) GO --创建索引 CREATE INDEX idx_ordertime ON t_order ( ordertime) GO --插入1000000条记录 WITH cte AS ( SELECT NUMBER + 1 AS NUMBER FROM master..spt_values a WHERE a.TYPE = 'P' AND NUMBER < 1000) INSERT INTO t_order (ordertime, productname) SELECT Getdate() - a.NUMBER, LEFT (Newid(),10) FROM cte a CROSS JOIN cte b GO

2，分别查询

?

SET STATISTICS io ON －－查询一采用变量 DECLARE @ date DATETIME SET @ date = Getdate() SELECT * FROM t_order WHERE ordertime > @ date GO －－查询二采用变量给出具体值 SELECT * FROM t_order WHERE ordertime > Getdate()

3，对比执行计划发现相差太太太太大了。

查询1扫描了整个表，查询2确实很好的一个seek加Look up

（三） 原因分析以及验证

1，原因分析

因为当你使用变量时，查询语句在编译时，并不做SET操作。换句话说，即是SET操作是编译完成后，执行的时候才执行。所以编译的时候MS SQL 并不知道◎date的值，所以不能产生一个正确的执行计划。

2,验证

MS SQL在这种情况总按照一个固定的估计值在产生执行计划（即30%），所以做一个全表扫描更划算。让我们来论证一下，我们对该表插入了1000000条记录，按照30% ，所以预估行数就该是300000,查看执行计划，果然如此（注意红色方框）：

（四）解决方案

解决方案1：（使用option(RECOMPILE)，在执行时重新编译）：

?

declare @ date datetime set @ date =GETDATE() select * from T_order where ordertime>@ date option (RECOMPILE)

解决方案2：给定一个参数提示给该查询

?

declare @ date datetime set @ date =GETDATE() select * from T_order where ordertime>@ date option (OPTIMIZE FOR (@ date = '2012-04-29' ))

解决方案3：封装成存储过程，有人就会疑问了，为什么存储过程可以呢？在这里大家别把参数和变量混淆了，在SQL SERVER里面写法都一样，但意义不完全一样。存储过程的编译实在第一次执行的时候才产生执行计划。

?

--创建存储过程 CREATE PROC Sp_select_t_order @ date DATETIME AS SELECT * FROM t_order WHERE ordertime > @ date GO --执行存储过程 DECLARE @ date DATETIME SET @ date = Getdate() EXEC Sp_select_t_order @ date

解决方案4：参数化查询

?

sp_executesql N 'select * from T_order where ordertime>@date' , N '@date datetime' , @ date = '2012-04-29'

以上四种解决方案的执行计划都如下，实际环境推荐封装成存储过程：