SQL: Does an indexed view always get updated for base table updates?

I had a forum question recently that asked if indexed views get updated when the base table gets updated, but the columns that are part of the view are unchanged.

The easiest way to check this is to try it. Let's start with this code to create a table and put some data in it:

USE tempdb;
GO

DROP VIEW IF EXISTS dbo.TestValueOnly;
GO

DROP TABLE IF EXISTS dbo.Test;
GO

CREATE TABLE dbo.Test
(
    TestID int IDENTITY(1,1) NOT NULL 
        CONSTRAINT PK_dbo_Test PRIMARY KEY,
    TestName varchar(50) NOT NULL,
    ValueToChange int NOT NULL
);
GO

INSERT dbo.Test (TestName, ValueToChange)
VALUES ('Hello', 12),
       ('There', 14),
       ('And', 16),
       ('Again', 18);
GO

USE tempdb;

DROP VIEW IF EXISTS dbo.TestValueOnly;

DROP TABLE IF EXISTS dbo.Test;

CREATE TABLE dbo.Test

(

TestID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_Test PRIMARY KEY,

TestName varchar(50) NOT NULL,

ValueToChange int NOT NULL

);

INSERT dbo.Test (TestName, ValueToChange)

VALUES ('Hello', 12),

('There', 14),

('And', 16),

('Again', 18);

Then we'll create a schema bound view on the table, and a clustered index on the view:

CREATE VIEW dbo.TestValueOnly
WITH SCHEMABINDING
AS
SELECT TestID, ValueToChange 
FROM dbo.Test;
GO

CREATE UNIQUE CLUSTERED INDEX CX_dbo_TestValueOnly
ON dbo.TestValueOnly (TestID);
GO

CREATE VIEW dbo.TestValueOnly

WITH SCHEMABINDING

SELECT TestID, ValueToChange

FROM dbo.Test;

CREATE UNIQUE CLUSTERED INDEX CX_dbo_TestValueOnly

ON dbo.TestValueOnly (TestID);

Finally, we'll try both types of updates:

UPDATE dbo.Test 
SET TestName = 'Changed' 
WHERE TestID = 14;

UPDATE dbo.Test 
SET ValueToChange = 17 
WHERE TestID = 14;

UPDATE dbo.Test

SET TestName = 'Changed'

WHERE TestID = 14;

UPDATE dbo.Test

SET ValueToChange = 17

WHERE TestID = 14;

The first updates only a column that's not part of the indexed view. The second updates a column that is part of it.

And, as expected, the SQL Server team was mighty clever, and it works exactly as you'd expect:

The first update only touches the base table. The second touches both.

Leave a Reply Cancel reply