What can cause index corruption in SQL Server indexed view

Question

I have a view with a clustered index on it.

sometimes, when I update Order table (using EF Core that uses transaction)

UPDATE dbo.Order SET OrderStatus = @orderStatus
INSERT INTO dbo.History VALUES (....) //no relationship to orders tabe

I get errors like

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

Rebuilding he view index does not help. When I drop and recreate the indexed view, the same update works.

Any idea what might be causing it?

I'm using SQL Server 2014 I've upgraded from SQL Server 2014 to 2022 Standard Edition and set compatibility level to latest.

---

View definition:

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        
    --status
    ((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))) --end status 

    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

Answer 1

Based on the schema and view definition, it appears that updating the order status adds a new row to the view (likely because it now satisfies the where condition). I believe the issue may be related to the group by clause, as it does not match the unique index definition, potentially allowing duplicated rows in the view.

To address this, try rewriting the view by grouping only by the unique index columns (MaterialCode and OrderedProductId). You can use a MIN aggregate function to retrieve data for the other fields in the original group by clause (OrderId, IsSHV, IsPT, OrderStatus, IOStatus).

Although it may seem that including OrderProductId in the group by clause would automatically make each row unique, there may be lack of foreign key constraints defined on tables that I am not aware of, and therefore, I cannot be certain.

It is possible that with this mismatch between the group by and index definition, the database engine could make incorrect assumptions when managing the index.

Answer 2

This error message does not indicate at all a corruption of your index. Index corruptions can be viewed with DBCC CHECKTABLE on the clustered index of the view... But I think you will not find any corruption.

Have you try to UPDATE with the same data from EFcore into SSMS ? I wont be surprise that SSMS will not raise any exception, while RF Core will... Because I suspect that the UPDATE in EFCore is not straight and perhaps do a "replace" that violate the unique key cnstraints of the clustered index...