如何创建物化视图，低成本获取按指定键分组的最新记录？

需要创建一个视图，从每个指定key对应数百条记录的底层表中，低成本查询到每个key的最新值。原本考虑物化视图（索引视图），看中两个核心特性：

• 借助聚簇索引实现低成本读取操作
• 仅在底层数据变更时刷新索引，且数据变更频率较低

但实际尝试中遇到两类限制：

1. 索引视图无法使用派生表与ROW_NUMBER()这类窗口函数，尝试的SQL如下：

CREATE VIEW dbo.vw_SubjectsLatestEmployeeCategory
WITH SCHEMABINDING
AS
select subjectId, category 
    from (
        select subjectId, category, row_number() over (partition by subjectId order by [DateFrom] desc) as rn 
        from [myTable]
    ) as x
    where rn = 1
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_SubjectsLatestEmployeeCategory
ON dbo.vw_SubjectsLatestEmployeeCategory (subjectId)

2. 尝试先创建存储最新日期的索引视图，再关联获取category，但索引视图不支持MAX()/MIN()这类非增量聚合函数，尝试的SQL如下：

CREATE VIEW dbo.vw_Subjects_RecordsLatestDate
WITH SCHEMABINDING
AS
select subjectId, MAX([DateFrom]) AS LatestDateFrom
from [myTable]
group by subjectId
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_Subjects_LatestDate
ON dbo.vw_Subjects_RecordsLatestDate(subjectId)

需求：如何创建物化视图（索引视图），低成本获取按指定键分组的表中最新记录？
排除方案：

• 创建带索引的普通表，通过数据库作业或触发器保持数据同步
• 在源表添加触发器

可以利用索引视图支持NOT EXISTS关联逻辑的特性，直接在视图中筛选出每个subjectId的最新记录，同时满足创建聚簇索引的要求。

实现代码 #

CREATE VIEW dbo.vw_SubjectsLatestEmployeeCategory
WITH SCHEMABINDING
AS
SELECT 
    t.subjectId,
    t.category,
    t.DateFrom
FROM dbo.myTable t
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.myTable t2 
    WHERE t2.subjectId = t.subjectId 
      AND t2.DateFrom > t.DateFrom
)
GO

-- 创建唯一聚簇索引，保障读取性能
CREATE UNIQUE CLUSTERED INDEX IX_vw_SubjectsLatestEmployeeCategory
ON dbo.vw_SubjectsLatestEmployeeCategory (subjectId, DateFrom)

关键说明 #

1. 逻辑有效性：NOT EXISTS子句确保当前记录的DateFrom是该subjectId下的最大值，即对应最新记录。
2. 索引视图兼容性：该写法完全符合SQL Server索引视图的规则，未使用禁止的派生表、窗口函数或非增量聚合函数。
3. 性能特性：聚簇索引(subjectId, DateFrom)会在底层数据变更时自动维护，查询时可直接通过索引定位目标数据，确保低成本读取。
4. 去重补充：如果DateFrom存在重复值，需添加源表的唯一标识列（如主键）到NOT EXISTS条件中，确保每个subjectId仅返回一条最新记录，示例调整如下：

   WHERE t2.subjectId = t.subjectId 
     AND (t2.DateFrom > t.DateFrom OR (t2.DateFrom = t.DateFrom AND t2.Id > t.Id))
   

内容的提问来源于stack exchange，提问作者Janeček Martin