A quick reference of the keywords a view definition must not contain if you want an indexed view in SQL Server, part 10 of my views series.
SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
Self Join is handy, but it is not allowed in indexed view definitions. Limitation of the view 9 reuses my employee and manager example to show it.
SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
Why is an outer join not allowed in indexed views? Rows can logically disappear when you insert into a base table. Limitation of the view 8 shows an example.
SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
An indexed view needs WITH SCHEMABINDING, and that rules out cross database queries. Limitation of the view 7 walks through it with two test databases.
SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
UNION not allowed in an indexed view can surprise you. Limitation of the view 6 shows the script that fails and how the OR operator is allowed instead.
SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
A view with COUNT(*) cannot get a clustered index, but switch to COUNT_BIG and it works. Limitation of the view 5, shown with a simple tempdb example.
SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
Why does SELECT * miss a new column? The adding column issue in view stays until you refresh it with sp_refreshview. Limitation of the view number 4.