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 (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – Outer Join in Indexed View – Question to Readers

  1. 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


  2. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31 Journey to SQLAuthority

  3. 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!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s