已有基表索引时，SQL Server视图是否需建索引提升查询性能？

基表结构 #

CREATE TABLE [dbo].[MyTable]
(
    [Row_Id] [bigint] NOT NULL, 
    [Year_Code] [varchar](100) NOT NULL,
    [Row_Values] [text] NULL,
    [Date_Refreshed] [datetime] NOT NULL,    

    CONSTRAINT [PK_MyTable_Row_Id] 
        PRIMARY KEY CLUSTERED([Row_Id] ASC) 
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]     
GO  

ALTER TABLE [dbo].[MyTable] 
    ADD CONSTRAINT [DF_MyTable_Date_Refreshed]
        DEFAULT (getdate()) FOR [Date_Refreshed] 
GO  

CREATE NONCLUSTERED INDEX [IX_MyTable_Code] 
ON [dbo].[MyTable] (Year_Code ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,  
          OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

视图定义 #

CREATE VIEW [dbo].[vw_MyTable] 
AS 
    SELECT 
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."id"') AS id,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."year"') AS  year_code,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."emp_id"') AS emp_id,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."emplocalid"') AS emplocalid,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."firstname"') AS firstname,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."lastname"') AS lastname,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."middlename"') AS middlename,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."dateofbirth"') AS dateofbirth,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."race"') AS race,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."gender"') AS gender     
    FROM 
        [dbo].[MyTable]

查询语句 #

SELECT 
    [id], [year_code], [emp_id],
    [emplocalid], [firstname], [lastname], [middlename],
    [dateofbirth], [race], [gender]   
FROM 
    [dbo].[vw_MyTable]  
WHERE
    [year_code] = 'yr2023'

性能优化解答 #

首先要明确：你当前的查询无法利用基表的IX_MyTable_Code索引。因为查询过滤的是视图中从Row_Values字段的JSON内容里提取的year_code，而非基表本身的Year_Code字段，SQL Server查询优化器无法直接关联这两个值，只能全表扫描后再解析JSON，性能自然受限。

是否需要给视图创建索引？答案是不一定要优先选择索引视图，更高效的优化方案有以下几种，按优先级排序：

1. 直接复用基表的Year_Code字段过滤 #

如果基表的Year_Code和JSON中的year值完全一致，直接修改查询条件，过滤基表的Year_Code字段：

SELECT 
    [id], [year_code], [emp_id],
    [emplocalid], [firstname], [lastname], [middlename],
    [dateofbirth], [race], [gender]   
FROM 
    [dbo].[vw_MyTable]  
WHERE
    [dbo].[MyTable].[Year_Code] = 'yr2023' -- 直接引用基表字段

或者修改视图，同时返回基表的Year_Code，再用这个字段过滤。这样查询优化器可以先通过IX_MyTable_Code索引快速筛选出符合条件的行，再解析JSON，性能会大幅提升。

2. 给基表添加计算列并创建索引 #

如果基表的Year_Code和JSON中的year值不一致，建议在基表上创建一个持久化计算列，提取JSON中的year值，然后给该列建索引：

-- 添加持久化计算列
ALTER TABLE [dbo].[MyTable]
ADD Json_Year AS JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."year"') PERSISTED;
GO

-- 给计算列创建非聚集索引
CREATE NONCLUSTERED INDEX IX_MyTable_JsonYear ON [dbo].[MyTable](Json_Year);
GO

之后修改视图，用这个计算列作为year_code字段，查询时就能直接利用新索引过滤数据，避免全表扫描。

3. 最后考虑创建索引视图 #

如果上述两种方案都不可行，才考虑创建索引视图。但索引视图有严格的要求，且会增加基表写入操作的开销（因为索引视图需要同步更新），具体步骤如下：

• 修改视图，添加SCHEMABINDING属性，同时包含基表的主键Row_Id（索引视图需要唯一聚集索引）：

DROP VIEW IF EXISTS [dbo].[vw_MyTable];
GO

CREATE VIEW [dbo].[vw_MyTable] 
WITH SCHEMABINDING -- 必须添加该属性
AS 
    SELECT 
        [Row_Id], -- 用于创建唯一聚集索引
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."id"') AS id,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."year"') AS year_code,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."emp_id"') AS emp_id,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."emplocalid"') AS emplocalid,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."firstname"') AS firstname,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."lastname"') AS lastname,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."middlename"') AS middlename,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."dateofbirth"') AS dateofbirth,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."race"') AS race,
        JSON_VALUE(CAST([Row_Values] AS varchar(8000)), '$."gender"') AS gender     
    FROM 
        [dbo].[MyTable]
GO

• 创建唯一聚集索引（索引视图必须先创建唯一聚集索引）：

CREATE UNIQUE CLUSTERED INDEX IX_vw_MyTable_RowId ON [dbo].[vw_MyTable](Row_Id);
GO

• 创建针对year_code的非聚集索引：

CREATE NONCLUSTERED INDEX IX_vw_MyTable_YearCode ON [dbo].[vw_MyTable](year_code);
GO

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