持久化计算列索引为何性能不及非持久化计算列索引？

我们有一张包含varchar(max)列的大表，需要用CHARINDEX函数检索该列内容，示例查询如下：

select x from y where charindex('string',[varchar_max_field]) > 0

为提升查询性能，我们基于CHARINDEX的结果创建了计算列，分别生成持久化和非持久化版本，并为每个版本创建仅包含该计算列的非聚集索引：

CREATE TABLE [schema].[table] (
  [other fields...]
  [State]  [NVARCHAR](MAX) NULL,  /* 存储JSON信息 */
  [NonPersistedColumn]  AS (CHARINDEX('something',[State],(1))),
  [PersistedColumn]     AS (CHARINDEX('something',[State],(1))) PERSISTED )

CREATE NONCLUSTERED INDEX [ix_NonPersistedColumn] ON [schema].[table]
    ([NonPersistedColumn]  ASC )
CREATE NONCLUSTERED INDEX [ix_PersistedColumn]    ON [schema].[table]
    ([PersistedColumn]     ASC )

测试时发现：

• 查询非持久化计算列时，按预期使用对应非聚集索引执行索引查找：

SELECT TOP (50) [NonPersistedColumn]  FROM [table]  WHERE [NonPersistedColumn]  > 0

• 但查询持久化计算列时，却使用了非持久化计算列的索引执行索引查找：

SELECT TOP (50) [PersistedColumn]  FROM [table]  WHERE [PersistedColumn]  > 0

• 如果强制使用持久化计算列的索引，查询会转为对包含ID列的聚集索引（PK索引）执行键查找，输出列表里包含[State]列，但实际上查询只请求了非聚集索引中的计算列。

疑问：

1. 为什么强制使用持久化索引时会用到PK索引？是否与PK总会被添加到非聚集索引有关？
2. 持久化计算列是否几乎无优势，还是遗漏了某些关键点？

1. 强制使用持久化索引时出现键查找的原因 #

SQL Server的非聚集索引默认会包含聚集索引键（即PK列）作为行定位符，但这不是核心原因。真正的问题是查询优化器对持久化计算列的索引存在误判：它错误地认为需要访问基表中的State列来重新计算PersistedColumn的值，即便该列已经被持久化存储。

当你强制指定使用ix_PersistedColumn时，优化器没有正确识别到该索引已经包含了持久化列的物理存储值，反而认为需要通过PK回到聚集索引中获取State列做重新计算——这完全是不必要的，因为你的查询只需要返回PersistedColumn本身，该值已经存在于非聚集索引里。

2. 持久化计算列的优势与遗漏的关键点 #

持久化计算列并非没有价值，只是当前场景没用到它的核心优势，同时你可能遗漏了以下关键点：

• 核心优势场景：当计算逻辑复杂（如复杂字符串操作、多函数嵌套），或者需要频繁基于该列查询、排序、分组时，持久化列会把计算结果物理存储在基表中，避免每次查询重复计算，能大幅降低CPU消耗——尤其适合State这类存储大JSON内容的列。
• 遗漏的优化点：
  1. 统计信息更新：持久化计算列的索引统计信息可能未及时更新，导致优化器认为非持久化索引的成本更低。可以手动更新统计信息：

     UPDATE STATISTICS [schema].[table] [ix_PersistedColumn]
     

  2. 索引的逻辑关联：优化器认为两个计算列的逻辑完全等价，所以会选择它认为成本更低的索引。可以尝试通过索引提示明确指定索引，或者在查询中使用表别名来强化列的归属，引导优化器选择正确的索引。
  3. 计算列的确定性验证：虽然CHARINDEX是确定性函数，但要确保State列的非空性、计算逻辑的确定性被优化器正确识别。如果State允许NULL，CHARINDEX会返回0，这部分逻辑的统计信息是否准确也会影响优化器的判断。
  4. 索引覆盖性确认：可以尝试在创建持久化列索引时，显式添加INCLUDE子句（哪怕不需要额外列），或者检查索引是否被正确创建为仅包含目标计算列，避免优化器产生不必要的依赖判断。

另外，查询持久化列时自动选择非持久化索引的原因是：优化器判定两个计算列的逻辑完全一致，会优先选择统计信息更优、成本估算更低的索引——如果非持久化索引的统计信息更新更及时，或者优化器对持久化列的存储开销判断有误，就会出现这种“跨索引”选择的情况。

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