Andrew Fraser DBA

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