SQL SERVER – Outer Join in Indexed View – Question to Readers

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)

SQL Index, SQL Joins, SQL View, SQL White Papers
Previous Post
SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query
Next Post
SQLAuthority News – FIX : An error occurred while executing this command. If this error persists, please contact your Live Meeting administrator.

Related Posts

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

    Reply
  • good tutorials….! continue with the blogs so that will be helpful for developers.

    Reply
  • Ringo Schplingo
    November 8, 2011 4:58 pm

    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!

    Reply
  • 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

    Reply

Leave a Reply