符合索引视图约束的分组取最大值方案（Azure SQL EAV表场景）

可行方案实现 #

这个需求存在符合要求的实现方案，核心利用自增ID的有序性结合索引视图支持的条件聚合逻辑完成最新值提取，未使用任何禁止的语法特性。

实现逻辑 #

AttributeValues表的Id为自增主键，同一个(Organization_Id, Attribute_Id)分组下，Id越大代表记录越新。我们将Id与Value拼接为字符串后取最大值，得到的结果必然是最新记录的拼接串，拆分后即可拿到对应的最新属性值。

具体实现代码 #

1. 创建符合约束的索引视图

CREATE VIEW dbo.vw_OrganizationAttributes
WITH SCHEMABINDING
AS
SELECT
    Organization_Id,
    -- 提取Attribute_Id=1对应的最新Description
    SUBSTRING(
        MAX(CASE WHEN Attribute_Id = 1 THEN CONCAT(Id, CHAR(1), Value) END),
        CHARINDEX(CHAR(1), MAX(CASE WHEN Attribute_Id = 1 THEN CONCAT(Id, CHAR(1), Value) END)) + 1,
        8000
    ) AS Description,
    -- 提取Attribute_Id=2对应的最新Title
    SUBSTRING(
        MAX(CASE WHEN Attribute_Id = 2 THEN CONCAT(Id, CHAR(1), Value) END),
        CHARINDEX(CHAR(1), MAX(CASE WHEN Attribute_Id = 2 THEN CONCAT(Id, CHAR(1), Value) END)) + 1,
        8000
    ) AS Title,
    -- 索引视图强制要求包含COUNT_BIG(*)
    COUNT_BIG(*) AS RecordCount
FROM dbo.AttributeValues
GROUP BY Organization_Id;

注：这里使用不可见字符CHAR(1)作为分隔符，避免和Value字段的常规内容冲突。

2. 创建视图的聚集索引

CREATE UNIQUE CLUSTERED INDEX IX_vw_OrganizationAttributes_OrgId
ON dbo.vw_OrganizationAttributes (Organization_Id);

后续操作 #

聚集索引创建完成后，该视图为实体化存储，完全支持在Description和Title字段上创建全文索引，即可使用CONTAINS()语法进行全文搜索，视图会自动随基表AttributeValues的数据更新同步。

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