Sql-server – SqlPackage.exe unnecessarily drops and recreates indexed view with schemabinding

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID, 
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
    SELECT 1 
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE 
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            ) 
            OR 
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            ) 
            OR 
            (
                [tInner].[Users_ImageObjectID] IS NULL 
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.

Create a permanent table to house the computed results:

CREATE TABLE dbo.ViewResult
(
    test_id INT NOT NULL
        CONSTRAINT PK_ViewResult
        PRIMARY KEY CLUSTERED
    , tMin AS DECIMAL(18,5)
    , tMax AS DECIMAL(18,5)
    , tAvg AS DECIMAL(18,5)
    , tStDev AS DECIMAL(18,5)
);

Create a SQL Server Agent job that performs the following actions:

TRUNCATE TABLE dbo.ViewResult;

INSERT INTO dbo.ViewResult    
SELECT 
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM dbo.test
    INNER JOIN dbo.samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id;

Schedule this Job to run as often as you need the "view" updated.

This will allow fast DML operations, while still providing reasonably fresh info about the data.