Indexed view is not updated on data changes in base table

Indexed view is not updated on data changes in base table by ellight1

Closed

as Duplicate Help for as Duplicate

Type:	Bug
ID:	771336
Opened:	11/15/2012 11:50:58 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Data in an indexed view (index) does not changes after table update.
This bug was reproduced in concrete use of MERGE.
The problem can be avoided by editing trigger in 2 ways:
- replacing MERGE with simple UPDATE,
- or cutting "WHEN NOT MATCHED" from MERGE

Link on russian forum:
http://www.sql.ru/forum/actualthread.aspx?bid=1&tid=983716&pg=-1

Also tested on:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Details (expand)

English

SQL Server 2008 SP3

SQL Engine

Windows Server 2008 (all editions) (SP2)

English

USE master
PRINT @@Version
GO
CREATE DATABASE [Test]                    -- Set valid folder path
    ON PRIMARY    (NAME = N'Test_Data'    ,FILENAME = N'Z:\Path\Test_Data.mdf')
    LOG ON        (NAME = N'Test_log'    ,FILENAME = N'Z:\Path\Test_log.ldf')
GO
USE Test    -- Not use tempdb!
GO
CREATE TABLE [dbo].[TAddition] (
     [ID]    Int    IDENTITY
     CONSTRAINT [PK_TAddition]        PRIMARY KEY
    ,[Data]    NVarChar(256)        NULL
)
GO
CREATE TABLE [dbo].[TObjectAddition] (
     [ID]        Int
     CONSTRAINT [PK_TObjectAddition]        PRIMARY KEY
--     CONSTRAINT [FK_TObjectAddition]        REFERENCES [dbo].[TObject]([ID])
    ,[Addition]    Int    NOT NULL
     CONSTRAINT [FK_TObjectAddition_Addition]    REFERENCES [dbo].[TAddition]([ID])
)
GO
CREATE VIEW [dbo].[VObjectAddition] WITH SCHEMABINDING AS
SELECT     O.ID
    ,O.Addition
    ,A.Data
FROM         dbo.TObjectAddition    O
    JOIN dbo.TAddition        A ON A.ID = O.Addition
GO
CREATE UNIQUE CLUSTERED INDEX [PK_VObjectAddition] ON [dbo].[VObjectAddition] ([ID])
GO
CREATE VIEW [dbo].[VObject] WITH SCHEMABINDING AS
SELECT     O.ID
    ,A.Addition
    ,A.Data
FROM             dbo.TObjectAddition    O    -- dbo.TAddition
    LEFT JOIN dbo.VObjectAddition    A WITH(NoExpand) ON A.ID = O.ID
GO
CREATE TRIGGER [dbo].[trVObject] ON [dbo].[VObject]
INSTEAD OF UPDATE AS BEGIN
    SET NOCOUNT ON

    IF Update(Data)
        MERGE    dbo.TAddition    T
        USING    Inserted    S ON T.ID = S.Addition
        WHEN     MATCHED        THEN UPDATE SET
             Data            = S.Data
        WHEN NOT MATCHED BY TARGET    THEN
            INSERT( Data)
            VALUES(S.Data);
END
GO
SET IDENTITY_INSERT dbo.TAddition ON
INSERT    dbo.TAddition(ID,Data)
SELECT    A.Number,'Data ' + Convert(VarChar,A.Number)
FROM    master.dbo.spt_values    A
WHERE    A.[type] = 'P'
SET IDENTITY_INSERT dbo.TAddition OFF

INSERT    dbo.TObjectAddition(ID,Addition)
SELECT    A.Number,A.Number
FROM    master.dbo.spt_values    A
WHERE    A.[type] = 'P'

UPDATE    V
SET    Data = 'Changed data'
FROM    dbo.VObject V
WHERE    ID = 1

SELECT * FROM dbo.TAddition                WHERE ID = 1
SELECT * FROM dbo.VObjectAddition WITH(NoExpand)    WHERE ID = 1
GO
USE master
GO
DROP DATABASE [Test]
GO

ID    Data
1    Changed data

ID    Addition    Data
1    1    Data 1

ID    Data
1    Changed data

ID    Addition    Data
1    1    Changed data

X64

0 attachments

Hello,
Thanks for your time reporting the issue.
Please, note this problem is known and was actually fixed in SQL Server 2008 SP3 Cumulative Update 8 (see http://support.microsoft.com/kb/2756471).
The fix will also be available in SQL Server 2008 R2 SP1 Cumulative Update 10, SQL Server 2008 R2 SP2 Cumulative Update 4, SQL Server 2012 Cumulative Update 5 and SQL Server 2012 SP1 Cumulative Update 2 when they release.
I will be resolving the issue as a duplicate of a known hotfix.

Thanks,
Alexey
SQL Server Development