Given an indexed view that has a
JOIN between two tables that already have a foreign key relationship along with a
WHERE predicate on the parent table.
When inserting into the parent table of the foreign key, the compiler is adding index maintenance to the plan, even though it is provable that no matching rows can exist.
Is this a bug, or perhaps a missed optimization? Or is there some logical or algebraic fallacy that I'm under?
CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null); CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED);
CREATE VIEW dbo.vChild WITH SCHEMABINDING AS SELECT c.Id, c.ParentId FROM dbo.Child c JOIN dbo.Parent p ON p.Id = c.ParentId -- WHERE p.SomeCol = 0; -- problem dependent on this line
CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)
db<>fiddle with WHERE
db<>fiddle without WHERE
At this stage, any
UPDATE affecting columns in the view, and any
DELETE, of the view's tables quite rightly trigger view maintenance. The compiler will take the modified rows, spool them up and feed them through the view's joins, outputting any results into the view's index.
The same can be said for insertions to
Child, because a row could exist for
Parent already (that qualifies against the
WHERE), and therefore the new
Child row could qualify for the join.
When inserting into
Parent, it is provable that index maintenance does not need to be done. A matching row cannot yet exist in
Child because of the foreign key relationship, so there can therefore be no rows from the insert that qualify for the view.
If you were to run the following script, you will see that no view maintenance is done.
INSERT Parent (SomeCol, OtherCol) VALUES (0, 100);
Quite clearly, the compiler can deduce that view maintenance is not needed here.
However, if you were to uncomment the line
WHERE p.SomeCol = 0 in the view definition, you suddenly get view maintenance. So the addition of another column to the view, which is not a joining column and does not have a foreign-key relationship, causes this. This is despite the same relational logic applying: the insert should still provably not qualify for the view, because of the foreign-key column that is still there.
Oddly enough, the compiler can still recognize some cases where the insert will not qualify for the view (and despite this particular example being auto-parameterized).
Here the compiler recognizes that
SomeCol fails the
WHERE, and there is no need to do index maintenance.
INSERT Parent (SomeCol, OtherCol) VALUES (1, 100);