[Solved] Alternative for full outer join in indexed view


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.

4

solved Alternative for full outer join in indexed view