前言
上一篇我们讲完SQL动态查询，本节我们继续来讲解SQL动态查询中存在的问题。
SQL动态查询条件筛选过滤
当我们创建存储过程调用存储过程时，若筛选条件有值则过滤，没有值则返回所行记录，类似如下查询：
WHERE (SomeColumn=@col OR @col 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
对于动态SQL条件筛选过滤我们利用WHERE 1 = 1来拼接。接下来我们使用一般SQL语句和动态查询并比较其IO，如下：
SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2 FROM TestWHERE 1 =1AND (SomeCol2=@col OR @col IS NULL) GO DECLARE @col INTSELECT @col = 1 DECLARE @SQL NVARCHAR(4000) SET @SQL = 'SELECT SomeCol2 FROM Test WHERE 1 =1' IF @col IS NOT NULL SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol ' EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col SET STATISTICS IO OFFGO
看来执行一般SQL语句不会走索引查找，将导致性能问题，在开头我们就讲过筛选条件有值则过滤，无值则查询所有数据，那么我们完全可以借助ISNULL来查询，下面我们用ISNULL来改变一般语句筛选条件，看看是否会走索引查找呢？
SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2FROM dbo.TestWHERE 1 = 1AND SomeCol2 = ISNULL(@col,SomeCol2)
我们看到结果依然是走索引扫描，没有任何改变。是不是就没有解决之道了呢？我们来改变一般SQL语句查询方式，如下：
DECLARE @col INTSELECT @col = 1IF @Col IS NULL SELECT SomeCol2 FROM Test WHERE 1 = 1ELSE SELECT SomeCol2 FROM dbo.Test WHERE 1 = 1 AND SomeCol2 = @colGO
如上只能是勉勉强强解决了问题，因为只是针对一个参数，如果有多个参数要进行IF...ELSE..,那可就傻逼了。从本质上解决这个问题我们需要利用可选项重新编译。如下：
SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2 FROM dbo.TestWHERE 1 =1AND (SomeCol2 = @col OR @col IS NULL)OPTION(RECOMPILE) GO DECLARE @col INTSELECT @col = 1 DECLARE @SQL NVARCHAR(4000) SET @SQL = 'SELECT SomeCol2 FROM dbo.Test WHERE 1 =1' IF @col IS NOT NULL SET @SQL = @SQL + ' AND SomeCol2 = @InnerParamcol ' EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col SET STATISTICS IO OFFGO
总结
当利用条件筛选过滤数据时，如果条件有值则过滤，否则返回所有行记录。如果执行一般SQL语句和动态SQL，那么动态SQL会走索引查找，而一般SQL语句将导致索引扫描，此时需要加上OPTION(RECOMPILE)才走索引查找。