Monday, May 9, 2016

Why can’t I use Outer Join in an Indexed View?

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 TablesCREATE TABLE BaseTable (ID1 INTCol1 VARCHAR(100))CREATE TABLE JoinedTable (ID2 INTCol2 VARCHAR(100))GO-- Insert Values in TablesINSERT INTO BaseTable (ID1,Col1)SELECT 1,'First'UNION ALLSELECT 2,'Second'GOINSERT INTO JoinedTable (ID2,Col2)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'GO-- Use Outer JoinSELECT jt.*FROM BaseTable btRIGHT OUTER JOIN JoinedTable jt ON bt.ID1 jt.ID2WHERE bt.ID1 IS NULLGO
The script above will give us the following output:
SQL SERVER - Outer Join Not Allowed in Indexed Views outerjoinmiss1
-- Now Insert Rows in Base TableINSERT INTO BaseTable (ID1,Col1)SELECT 3,'Third'GO-- You will notice that one row less retrieved from JoinSELECT jt.*FROM BaseTable btRIGHT OUTER JOIN JoinedTable jt ON bt.ID1 jt.ID2WHERE bt.ID1 IS NULLGO-- Clean upDROP TABLE BaseTableDROP TABLE JoinedTable
GO
SQL SERVER - Outer Join Not Allowed in Indexed Views outerjoinmiss2
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.

No comments:

Post a Comment