Today I have question for you. Just a day ago I was reading whitepaper Improving Performance with SQL Server 2008 Indexed Views. Following is question and answer I read in the white paper.
Q. Why can’t I use OUTER JOIN in an indexed view?
A. Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.
Here I would like to ask you one question, do you have example for kind of OUTER JOIN where you insert data into base table, it will make a row disappear from query?
Please post your answer as comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
One the situion is like this.
When we have two tables, one for list of employee and the second one the list of retired employee.
then the rows from following query disppear when we add record in retired table
select e.id ,e.name
from dbo.employee e
left join RetiredEmployee j on e.id=j.id
where j.id is null
good tutorials….! continue with the blogs so that will be helpful for developers.
To me this makes little sense, an outer join is LESS LIKELY to make rows disappear from a recordset as it is less restrictive. For example if I inner join from say a fact table to a dimension table, should the dimension table not contain all values which are in the fact table, then not all rows which are in the fact table will be returned, whereas using a left join from the fact table will ensure that all results are returned!
isnt that right ?
you just pull out a table of all employees including retireds (Which mean j.id never null), then you set ‘where j.id is null’… then ofc the following query will disappear