SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

This blog post was previously published over here. I am republishing it in the series Limitation of the Views with a few modifications.

While reading the white paper Improving Performance with SQL Server 2008 Indexed Views, I noticed that it says outer joins are NOT allowed in the indexed views. Here, I have created an example to demonstrate why this is so.

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 that 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 I mentioned earlier, I believe this is an expensive way to manage the same issue as to why it is not allowed in Indexed View.

Additionally, SQL Server Expert Ramdas provided excellent explanations regarding NULL and why resultset maintenance is expensive, over here.

“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.”

This series is indeed getting very interesting. What are your suggestions?

Reference: Pinal Dave (https://blog.sqlauthority.com)