SQL Server索引视图出现唯一键重复错误的原因排查

问题场景 #

我有一个带聚集索引的视图，在使用EF Core通过事务更新Order表时（执行SQL如下），偶尔会触发唯一键重复错误：

UPDATE dbo.Order SET OrderStatus = @orderStatus
INSERT INTO dbo.History VALUES (....) -- 该表与Order表无关联

报错信息 #

Cannot insert duplicate key row in object 'MyView' with unique index 'MyClusteredIndex'

重建视图索引无法解决问题，但删除并重新创建索引视图后，相同的更新操作就能正常执行。

环境说明：已从SQL Server 2014升级至2022标准版，数据库兼容级别设为最新。

视图及索引定义 #

视图创建语句 #

CREATE VIEW [manufacture].[MaterialNeededForOrdersByIO]
WITH SCHEMABINDING
AS
    SELECT Order.ID,
        OrderedProductMaterials.OrderedProductId, 
        MaterialEnum.MaterialCode, 
        Needed = SUM(IIF(Order.STATUS <> 22 AND ISNULL(OrderedProducts.STATUS, 0) <> 22, ISNULL(OrderedProductMaterials.Quantity * OrderedProducts.Quantity, 0), 0)), 
        NeededAll = SUM(ISNULL(OrderedProductMaterials.Quantity * OrderedProducts.Quantity, 0)),
        IsSHV = IIF(Order.CustomerId = 2472 AND OrderedProducts.ProductionDate IS NULL, 1, 0),
        IsPT = IIF(Order.CustomerId IN(2566, 2662) AND OrderedProducts.ProductionDate IS NULL, 1, 0), 
        OrderStatus = Order.Status,
        IOStatus = OrderedProducts.Status,
        COUNT_ = COUNT_BIG(*)
    FROM dbo.Order
        INNER JOIN dbo.OrderedProducts ON Order.ID = OrderedProducts.IDZákazky
        INNER JOIN dbo.OrderedProductMaterials ON OrderedProducts.ID = OrderedProductMaterials.OrderedProductId 
        INNER JOIN dbo.MaterialEnum ON MaterialEnum.ID = OrderedProductMaterials.MaterialId 
    WHERE        
    -- 状态过滤条件
    ((Order.STATUS IN(3, 16, 19, 21, 22) AND ISNULL(OrderedProducts.STATUS, 0) = 0)
    OR (Order.STATUS NOT IN(1, 2, 20, 12, 14) AND OrderedProducts.STATUS IN(3, 16, 19, 21, 22))) -- 状态过滤结束

    GROUP BY OrderedProductMaterials.OrderedProductId, 
            MaterialEnum.MaterialCode, 
            Order.ID, 
            IIF(Order.CustomerId = 2472 AND OrderedProducts.ProductionDate IS NULL, 1, 0),
            IIF(Order.CustomerId IN(2566, 2662) AND OrderedProducts.ProductionDate IS NULL, 1, 0),
            Order.Status,
            OrderedProducts.ProductionDate, OrderedProducts.Status
GO

聚集索引创建语句 #

CREATE UNIQUE CLUSTERED INDEX [IX_MaterialNeededForOrdersByIO] ON [manufacture].[MaterialNeededForOrdersByIO]
(
    [MaterialCode] ASC,
    [OrderedProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

可能的原因分析 #

1. 索引键与分组逻辑不匹配 #

视图的聚集索引键是MaterialCode + OrderedProductId，但视图的GROUP BY子句包含了更多字段（比如Order.ID、OrderedProducts.ProductionDate等）。这意味着同一个MaterialCode+OrderedProductId组合可能对应多行分组结果，而聚集索引要求该组合唯一。只有当这些额外分组字段的取值在实际数据中不会导致同一索引键组合出现多行时，索引才能正常工作。

当Order.Status被更新时，可能触发分组字段的取值变化，使得原本被合并的分组被拆分，或拆分的分组被合并，此时若出现同一MaterialCode+OrderedProductId对应多行的情况，就会触发唯一键冲突。而重建视图时会重新计算所有分组数据，强制保证索引键唯一，所以暂时解决了问题。

2. 索引视图维护逻辑异常 #

SQL Server在更新基表时会自动维护索引视图的数据。如果维护过程中出现逻辑错误（比如并发场景下的竞态条件、索引视图的计算逻辑与基表数据同步不一致），可能导致重复键被插入。重建视图相当于重置了索引视图的数据集，暂时规避了维护逻辑的异常。

3. SQL Server版本升级后的兼容性问题 #

从2014升级到2022后，索引视图的维护机制、查询优化器逻辑有变化。某些在2014中能正常工作的分组/索引组合，在新版本中可能因为优化器的行为改变，导致维护索引视图时出现数据不一致。

4. 并发事务的影响 #

如果多个事务同时更新同一Order相关的数据，可能在索引视图维护时出现数据竞争，导致重复键被插入。而重建视图是在无并发干扰的情况下重新构建数据，所以不会出现冲突。

建议的排查/解决步骤 #

• 调整索引键与分组逻辑的一致性：确认MaterialCode+OrderedProductId是否能唯一确定视图的分组行。如果不能，需要修改索引键，加入GROUP BY中的其他字段（比如Order.ID、OrderedProducts.ProductionDate）来确保唯一性。
• 启用索引视图的统计信息自动更新：将索引的STATISTICS_NORECOMPUTE设为ON，确保优化器能获取准确的统计数据，避免维护逻辑出错。
• 跟踪并发操作：使用扩展事件或SQL Profiler跟踪出现错误时的并发事务，确认是否存在事务竞争问题。
• 临时禁用索引视图：暂时禁用索引视图，直接查询基表验证是否还会出现错误，以此确认是否是索引视图维护导致的问题。

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