As readers of SSWUG you probably know what a Materialize View is, at least in a database engine. A materialized view is a view on which an Index has been created, thus materializing the data resulting from the view.
If a query uses the view, and the data needed to fulfill the query is contained in the index, SQL Server does not have to exercise the query defined by the view; instead it may use the data contained in the index to fulfill the query instead. Often this can increase performance when retrieving the data. It can reduce performance when saving data to table on which the view consists because the view must be executed every time the underlying data is modified.
Note: In most versions of SQL Server you must include the phrase WITH (NOEXPAND) when you define the source of your select query (FROM, JOIN) in order for it to use the index. Do a search on TSQL NOEXPAND for more details.
From a Resilience perspective, there is a broader meaning to the term Materialized View. A materialized view does not have to be an SQL View with a supporting instance. A Materialized View may be implemented by data marts, data warehouse schemas, No-SQL storage, serialized data, etc. The point is that there is a separation from INSERT and UPDATE actions from SELECT actions. If the read operations are supported by different objects, if the load increases on data creation, it should not impact the reading of data if implemented in a distributed fashion.
How does the use of materialized views contribute to system resilience? Anything that can separate an activity into something that may be separated out into a different subsystem increases Application Resilience. An application that has a separation of data entry and reporting may continue to perform reporting activities, even when data entry processes are interrupted. View materialization can also be separated from data entry, resulting in a more Resilient system.
Have you used Materialized (Indexed) Views? Why not share your experience with us? This is an area that can have a lot of pitfalls you may wish to pass on. Simply leave a note here online, or drop an email to me, btaylor@sswug.org.
Cheers,
Ben