What to replace left join in a view so i can have an indexed view?

Here is an alternative. You want a materialized view of A not containing B. That isn't directly available... so instead, materialize two views. One of all A's and one of only A's with B's. Then, get only A's not having B's by taking A except B. This can be done efficiently:

Create two materialized views (mA and mAB) (edit: mA could just be the base table).mA lacks the join between A and B (thus containing all A's period [and therefore containing those records WITHOUT matches in B]).mAB joins between A and B (thus containing only A's with B's [and therefore excluding those records WITHOUT matches in B]).

To get all A's without matches in B, mask out those that match:

with ids as (  select matchId from mA with (index (pk_matchid), noexpand)  except  select matchId from mAB with (index (pk_matchid), noexpand))select * from mA a join ids b on a.matchId = b.matchId;

This should yield a left anti semi join against both your clustered indexes to get the ids and a clustered index seek to get the data out of mA you are looking for.

Essentially what you are running into is the basic rule that SQL is much better at dealing with data that IS there than data that ISN'T. By materializing two sources, you gain some compelling set based options. You have to weigh the cost of these views against those gains yourself.

There is a "workaround" here that involves check for NULL in the join and having a NULL representation value in the table

NULL value

INSERT INTO Father (Father_id, Father_name) values(-255,'No father')

The join

JOIN [dbo].[son] s on isnull(s.father_id, -255) = f.father_id

I don't think there is a good workaround. What you can do about this is to create a real table from the view and set indexes on that. This can be done by a stored procedure that is called regularly when data is updated.

Select * into <REAL_TABLE>From <VIEW>create CLUSTERED index <INDEX_THE_FIELD> on <REAL_TABLE>(<THE_FIELD>)

But this is only a noteworthy approach if data isn't updated every few seconds.