I recently received an email that contains a question from one of my readers. I have already replied the answer to his email, but I would still like to bring it to your attention and ask if you think I could have done any better with the example I gave.
The question was raised when the email sender read the white paper, Improving Performance with SQL Server 2008 Indexed Views. If you scroll all the way down through the said white paper, there are several questions and answers.
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 the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement. In addition, the performance of the implementation would be slower than for views based on standard (INNER) JOIN.
The reader was confused with my answer and wanted me to explain it further. Here is the example which I have quickly put together to demonstrate the behavior described in the above statement.
USE tempdb
GO
-- Create Two Tables
CREATEÂ TABLE BaseTable (ID1 INT, Col1 VARCHAR(100))
CREATEÂ TABLE JoinedTable (ID2 INT, Col2 VARCHAR(100))
GO
-- Insert Values in Tables
INSERTÂ INTO BaseTable (ID1,Col1)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO
INSERTÂ INTO JoinedTable (ID2,Col2)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
GO
-- Use Outer Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO
The script above will give us the following output:
-- Now Insert Rows in Base Table
INSERTÂ INTO BaseTable (ID1,Col1)
SELECT 3,'Third'
GO
-- You will notice that one row less retrieved from Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO
-- Clean up
DROPÂ TABLE BaseTable
DROPÂ TABLE JoinedTable
GO
After running this script, you will notice that as the base table gains one row, the result loses one row. Going back to the white paper mentioned earlier, I believe this is expensive to manage for the same reason why it is not allowed in Indexed View.
Let me know if you have a better example to demonstrate this behavior in the Outer Join.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
just 1 question ,won’t the similar effect be observed when using inner join , as taking the same example you used , 1 row will be added in the view as soon as the base table is updated .
So inner join should be costly too ?
Hi Paranjai,
Here is a disadvantage of using outer joins.
A disadvantage of outer joins in SQL is that they generate nulls in the result set. Those nulls are indistinguishable from other nulls that are not generated by the outer join operation. There is no “standard” semantics for nulls in SQL but in many common situations the appearance of nulls in outer joins doesn’t really correspond to the way nulls are returned and used in other places. Therefore the presence of nulls in outer joins creates a certain amount of ambiguity.
I think the reason Ramdas pointed is more pronouncing than what described in the main post. With either type of join when there’s a modification of data in the base tables the view has to bear the cost of update too. But the outer join will cause uncertainty whether null values are what from the base tables or because of the unmatched rows.
Hi Dave
How can i view in the database whether indexes’ exist or not?
SELECT * FROM SYS.INDEXES I
INNER JOIN SYS.Tables T
ON I.object_id = T.object_id
WHERE T.Object_Id = OBJECT_ID(‘table_name’) on which table you wanted to check whether indexes exist or not