Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”
Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems.
However they only work with inner joins. Attempts to create an indexed view with outer joins fail with:
Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.
This is quite a limitation of SQL Server – Oracle’s materialized views do not have this limitation.
One workaround is to manually create a table using the same SQL that you tried to use in the indexed view:
select col1, col2, col3 INTO myreportingtable from table1 left join table2 on ...
And then change the view definition to reference only that new table:
create view myview as select col1, col2, col3 from myreportingtable
That does mean a scheduled task or job will need to be created to refresh the new reporting table every night (or as required).
Code to create indexed views is like:
create view myview with schemabinding as select col1, col2, col3 from .... go create unique clustered index myindex on myview (col1, col2, col3 ) go