why left join is not working on indexed view

LEFT JOIN is disallowed in indexed views because it breaks determinism and row-count stability, which SQL Server requires to maintain indexed view consistency during DML. Indexed view only allows us to use Inner join other joins like right, left or full outer joins are not allowed in index view.

An indexed view must guarantee all four of these at all times

Ø  Deterministic output

Ø  Stable row cardinality

Ø  Incremental maintenance

Ø  Exact row-to-row dependency

LEFT JOIN violates all of the above.

The Core Problem is NULL Row Generation

 Let’s see the demo

Creating two table and inserting few records.

create table A (id int not null) create table B (id int not null) insert into A(id) values (1),(2),(3),(4),(5) insert into B(id) values (1),(3),(5),(6),(7)

Now creating a view.

CREATE VIEW dbo.vw_demo_left_join WITH SCHEMABINDING AS SELECT A.ID as aid, B.id as bid FROM dbo.A A LEFT JOIN dbo.B B ON A.ID = B.ID; GO

View created successfully.

Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_demo_left_join ON dbo.vw_demo_left_join(aid);

Not able to create index on this view.

Basically, the problem with That NULL row is artificial.

Bid are null for two rows.

When a row changes in B, SQL Server must do delta maintenance, not full recompute. With LEFT JOIN insert into B may remove a NULL row or replace it with a real row or delete from B may create a new NULL row. This is non-incremental behavior. That’s unacceptable for index maintenance. Even if data doesn’t change, future inserts can retroactively change past results. That violates the immutability guarantee required by indexed views.

INNER JOIN guarantees for row exists in both tables and no artificial rows created and no NULL row synthesis and stable row dependency. SQL Server can map Base table row and Indexed view row.

Let’s try with inner join.

No null row generated.

Creating a view

CREATE VIEW dbo.vw_demo_join WITH SCHEMABINDING AS SELECT A.ID as aid, B.id as bid FROM dbo.A A JOIN dbo.B B ON A.ID = B.ID; GO

View created successfully.

Now creating index.

CREATE UNIQUE CLUSTERED INDEX IX_demo_join ON dbo.vw_demo_join(aid);

Index created successfully.

Now see the view.