使用CASE WHEN为何触发index scan？有无可行替代方案？

问题根源 #

你遇到的情况是因为WHERE子句中用CASE包裹了索引列，SQL Server无法直接利用该列上的索引进行Seek操作——CASE表达式的结果是计算值，数据库无法将其与索引列的原始值做直接匹配，只能通过扫描整个索引/表来筛选数据，导致逻辑读取量更高。

可行替代方案 #

1. 将CASE逻辑拆解为等价布尔表达式（优先选择）

绝大多数复杂CASE的筛选逻辑，都能转化为直接的布尔条件组合，这是最高效的优化方式。

• 你的示例中，查询A的CASE完全等价于查询B的直接条件判断，所以直接替换成WHERE MobileNumber = LTRIM(RTRIM('987654321'))即可触发索引Seek。
• 针对更复杂的多分支CASE：
  原CASE写法：

  SELECT * FROM [dbo].[Mobile]
  WHERE CASE
          WHEN Status = 1 AND MobileNumber LIKE '13%' THEN 1
          WHEN Status = 2 AND LEN(MobileNumber) = 11 THEN 1
          ELSE 0
        END = 1
  

  等价拆解为：

  SELECT * FROM [dbo].[Mobile]
  WHERE (Status = 1 AND MobileNumber LIKE '13%') 
     OR (Status = 2 AND LEN(MobileNumber) = 11)
  

  这种写法让SQL Server可以根据条件匹配对应的索引（比如创建(Status, MobileNumber)复合索引），从而触发Seek。

2. 持久化计算列+索引

如果CASE的业务规则固定且频繁使用，可以创建持久化计算列并在其上建索引：

-- 添加持久化计算列，将CASE逻辑固化
ALTER TABLE [dbo].[Mobile] 
ADD IsValid AS CASE
                WHEN Status = 1 AND MobileNumber LIKE '13%' THEN 1
                WHEN Status = 2 AND LEN(MobileNumber) = 11 THEN 1
                ELSE 0
              END PERSISTED;

-- 在计算列上创建索引，包含查询需要的列
CREATE NONCLUSTERED INDEX IX_Mobile_IsValid 
ON [dbo].[Mobile](IsValid)
INCLUDE (MobileNumber, Status, /* 其他SELECT需要的列 */);

之后直接用WHERE IsValid = 1查询，就能触发索引Seek。

3. 参数化动态SQL（针对极复杂分支）

如果业务规则分支极多且无法统一为布尔表达式，可以用参数化动态SQL拼接条件，让每个分支生成最优查询计划：

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM [dbo].[Mobile] WHERE 1=1 ';
DECLARE @MobileNumber VARCHAR(20) = LTRIM(RTRIM('987654321'));
DECLARE @Status INT = 1;

IF @Status = 1
    SET @SQL += 'AND MobileNumber LIKE ''13%'' ';
IF @Status = 2
    SET @SQL += 'AND LEN(MobileNumber) = 11 ';
IF @MobileNumber IS NOT NULL
    SET @SQL += 'AND MobileNumber = @MobileNumber ';

EXEC sp_executesql @SQL, 
                   N'@MobileNumber VARCHAR(20)', 
                   @MobileNumber = @MobileNumber;

注意必须使用参数化查询避免SQL注入，且只有在逻辑极度复杂时才考虑这种方式。

4. 覆盖索引优化扫描效率

如果不得不保留CASE结构，可以创建覆盖索引将表扫描转为索引扫描，降低逻辑读取量：

CREATE NONCLUSTERED INDEX IX_Mobile_Covering
ON [dbo].[Mobile](MobileNumber)
INCLUDE (/* 所有SELECT需要的列 */);

即使还是索引扫描，逻辑读取也会远低于表扫描。

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