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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
Then we'll create a schema bound view on the table, and a clustered index on the view:
1 2 3 4 5 6 7 8 9 10 |
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 |
Finally, we'll try both types of updates:
1 2 3 4 5 6 7 |
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.