Prince Prince - 5 months ago 43x
SQL Question

Alternative for full outer join in indexed view

I want to display records from two tables.

It should return all the matching records from both the tables.

If a record is present in first table and not present in second table it should return null from the Second table and records from the first table.
If a record is present in second table and not present in first table it should return null from the first table and records from the second table

I don't want to use a

full outer join
, because I want to create an indexed view and outer(Left, Right, Full) joins are not allowed in Indexed View.

Is there any better solution for this scenario.


There is a workaround for indexed views with 'emulated outer joins'. However, it's hideous and I wouldn't advocate it.

A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the outer join in the query calling the views.

Yet another solution is to not create an indexed view, but an actual table (which you can keep up-to-date with triggers) and put indexes on that. Since you're materializing the view anyway, this might be the best solution.

In all likelihood, an indexed view (whichever method you choose) won't give you the (performance) result you are looking for (though the last method where you create an actual table might). Can you explain why you feel you need an indexed view in this particular case?

By the way, here is a (short) explanation why indexed views don't allow outer joins.