SQL Server："boolean“WHERE子句中的子查询

Question

我有一个使用以下结构的T-SQL查询(SQL Server 2008/2012)：

WHERE (@param1 IS NULL OR column1 = @param1)
AND (@param2 IS NULL OR column2 = @param2)
AND (@param3 IS NULL OR column3 = @param3)

这可以很好地工作，并自动优化WHERE子句中带有NULL参数的and‘’ed部分。

但是，当我对子查询执行同样的操作时，优化器似乎仍然执行子查询，即使参数为NULL：

WHERE 
    (@param1 IS NULL OR column1 IN (SELECT column 
                                    FROM table 
                                    WHERE column = @param1))
    AND (@param2 IS NULL OR column2 IN (SELECT column 
                                        FROM table 
                                        WHERE column = @param2))
    AND (@param3 IS NULL OR column3 IN (SELECT column 
                                        FROM table 
                                        WHERE column = @param3))

我的问题是，既然参数为NULL的事实应该已经“短路”了查询的那一部分，为什么优化器还要考虑"IN“SELECT呢？

Answer 1

@user640466:我认为如果添加OPTION (RECOMPILE)查询提示，坏计划就会消失，原因是查询优化器必须评估IN子句来获得ProductID值，而在OPTION (recompile)的情况下，它可以嗅探运行时值。请看下面一份来自Adventure的工作副本：

CREATE PROC MyTest
@ProductID INT ,
@Name NVARCHAR(50),
@ProductNumber NVARCHAR(50)
AS
SELECT * 
FROM Production.Product 
WHERE 
(ProductID IN (SELECT ProductID FROM Production.Product WHERE ProductID = @ProductID ) OR @ProductID IS NULL)
AND 
(Name IN (SELECT Name FROm Production.Product WHERE Name = @Name) OR @Name IS NULL)
AND 
(ProductNumber IN (SELECT ProductNumber FROM Production.Product WHERE ProductNumber = @ProductNumber)  OR @ProductNumber IS NULL)
--OPTION (RECOMPILE)
GO 

如果您执行Proc : EXEC Mytest 1，NULL，NULL，您将获得一个冗长的计划，但是，更改sp并取消注释选项(重新编译)，您将获得很好的计划。