如何基于变量为WHERE子句添加条件并保证查询性能？

你的问题根源在于SQL Server查询优化器无法为这种包含NOT(@param = X) OR 列条件的逻辑生成针对不同参数值的最优执行计划——它会生成一个通用计划，没法有效利用索引，尤其是数据量较大时。以下是几种不用字符串插值、保留原始查询结构的优化方法：

1. 添加OPTION (RECOMPILE)提示 #

这是最简单直接的方法，让SQL Server每次执行时根据当前参数值重新生成最优执行计划，避免通用计划的低效问题。修改后的查询如下：

SELECT ID, FirstName, LastName, Age, Email
FROM People P
WHERE (NOT (@inputParam = 0) OR (P.FirstName = 'John'))
  AND (NOT (@inputParam = 1) OR (P.LastName = 'Smith'))
  AND (NOT (@inputParam > 2) OR (P.Age > 28))
OPTION (RECOMPILE)

注意：如果这个查询执行频率极高，每次重编译会带来额外开销，适合执行频率中等的场景。

2. 用CASE表达式重构条件逻辑 #

将OR条件替换为CASE表达式，让查询优化器更容易识别可利用索引的条件：

SELECT ID, FirstName, LastName, Age, Email
FROM People P
WHERE 1 = CASE
           WHEN @inputParam = 0 AND P.FirstName <> 'John' THEN 0
           WHEN @inputParam = 1 AND P.LastName <> 'Smith' THEN 0
           WHEN @inputParam > 2 AND P.Age <= 28 THEN 0
           ELSE 1
         END

这种结构能让优化器根据参数值快速过滤不符合条件的行，更高效地使用索引。

3. 使用参数化查询结合执行计划引导 #

如果你的参数值有固定的几种组合，可以用OPTION (OPTIMIZE FOR (@inputParam = X))来指定针对特定参数值生成计划，或者用OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))避免参数嗅探问题，但后者需要结合实际场景测试。

比如针对@inputParam=0的场景优化：

SELECT ID, FirstName, LastName, Age, Email
FROM People P
WHERE (NOT (@inputParam = 0) OR (P.FirstName = 'John'))
  AND (NOT (@inputParam = 1) OR (P.LastName = 'Smith'))
  AND (NOT (@inputParam > 2) OR (P.Age > 28))
OPTION (OPTIMIZE FOR (@inputParam = 0))

如果需要支持多个参数值，可以考虑创建多个查询变体，或者用OPTIMIZE FOR UNKNOWN让优化器基于统计信息生成计划。

4. 确保相关列有合适的索引 #

不管用哪种方法，为FirstName、LastName、Age这些过滤列创建单独索引或者包含必要字段的覆盖索引，能大幅提升查询性能。比如：

CREATE NONCLUSTERED INDEX IX_People_FirstName ON People(FirstName) INCLUDE(ID, LastName, Age, Email)
CREATE NONCLUSTERED INDEX IX_People_LastName ON People(LastName) INCLUDE(ID, FirstName, Age, Email)
CREATE NONCLUSTERED INDEX IX_People_Age ON People(Age) INCLUDE(ID, FirstName, LastName, Email)

也可以根据实际参数使用频率创建复合索引，比如如果经常同时过滤FirstName和LastName，可以创建包含这两列的复合索引。

内容的提问来源于stack exchange，提问作者amedina