34,838
社区成员




日常在业务中经常用到类似 WHERE Column = @Param OR @Param IS NULL 的语句
CREATE TABLE Test
( SomeCol1 INT NOT NULL ,
Somecol2 INT NOT NULL )
INSERT Test SELECT number , low
FROM master..spt_values WHERE TYPE = 'p'
CREATE INDEX ix_col2 ON Test(Somecol2)
GO
这时查询时
DECLARE @col INT
SELECT @col = 1
SELECT SomeCol2
FROM Test
WHERE SomeCol2=@col OR @col IS NULL
看查询计划的结果是 全表扫描
但是如果添加OPTION(RECOMPILE) 后
出来的查询计划是索引查找。
麻烦问下大家是什么造成了这种情况呢?是否只有添加OPTION(RECOMPILE) ,或者使用拼接 来规避这种情况呢,重编译按理来说应该是增加了开销,平时大家有用或者那些场景下使用这个参数吗。
楼上大佬们说了不少,不过针对楼主提问的“是什么造成了这种情况呢?”。没有做针对说明,萌新这里随便瞎猜点,若有错误还请指点。
对于查询,sqlserver区分了"简单"查询和"正常"查询。基于此概念,我有两个猜测:
1、“简单”的查询用另外一套流程规则生成查询计划,它生成查询计划时,无法优化条件中的“or 变量 运算符 值”。
2、没有联结查询的属于“简单”查询
我是基于什么提出这两个猜测的?
1、如果你用查询提示with(forceseek),此时它会提示:“....查询处理器未能生成查询计划.....”。而你改为“OR SomeCol2 IS NULL ”或者“OR 1 IS NULL ”,它又行了。
2、如果你引入联结查询譬如inner join或cross apply,它会走seek,不再报错。但是left join仍然会报错,这一点想不通。
一个条件用判断,多个条件用动态脚本拼接
一个条件:
if @col is not null
SELECT SomeCol2
FROM Test
WHERE SomeCol2=@col
多个条件:
declare @sql nvarchar(max)
set @sql=N'SELECT SomeCol2
FROM Test where 1=1'+case when @col>'' then ' and SomeCol2='''+@col+'''' else '' end
你这种写法,纯粹是开发人员的一种偷懒的做法,不可取。
一个条件还好,条件多了呢?加OPTION(RECOMPILE) 可能都走不了索引
拼SQL才是正确做法,程序里或存储过程里拼都行。
有用的条件才加上,没用的不加,不让SQL server 做无意义的事:帮你来判断条件到底要不要加