Materialized views are enabled by creating an index on a view definition. You have to define the view with Schema Binding enabled in order to create an index. Moreover, if you have a materialized view you cannon change the schema of a table without first disabling the view, because the view depends on the schema of the table(s) on which it has been defined to be constant.
I have used materialized views effectively from time to time. I will say that I can’t remember a time I used them when it was not necessarily the best implementation. It was the expedient implementation due to time constraints and resources.
Just like creating an index on a table slows down data modification queries, creating an index on a view has the same impact for all tables used in the query. Every time you change data that supports the view, the index for the view must be kept in sync with whatever changes are made.
For my personal preference, indexed views are cool, but are used only under duress. The need for an indexed view should be the SQL Server example of a code smell. Something is wrong or missing from your database if you need an indexed view. That’s a very general statement; so please don’t feel offended if you find you have a scenario where an indexed view is a good solution. If they were ALWAYS bad, I don’t think Microsoft would have implemented them.
Why not share a problem you experienced where indexed views were a solid solution? Leave your comment here, or drop an email to btaylor@sswug.org.
Cheers,
Ben