In a conversation with a colleague  we started discussing materialized views. Firstly, he wanted to know do materialized views exist in SQL server ? and if so, how are the implemented and maintained for decent performance.

 Materialized do exist in SQL server – commonly refered to as Indexed Views.

 It is worth creating outlining some definitions.

Materialized we mean that the data in the view is actually computed and stored.

View  means a virtual table where the contents are defined by a query. Apart from indexed views ,the “standard” view is not a stored dataset – but is a set of columns and rows where the data set is derived from the underlying query.

 As well as the storing of underlying data, the other main difference between a normal view and an Indexed view – is that the Indexed view has a unique clustered index.

As for performance – an indexed view can perform better given the circumstances. For example , if the dataset is not updated frequently and the query deals with large aggregations.

Or if there is large aggregation datasets with many joins and various queries are used to access different aspects of the data.

 If regular updates are required on the Indexed View – the maintenance level overheads may outweigh the benefits derived.

 In situations where there are OLTP type queries and also frequent updates – indexed views can cause performance to suffer.

 Ensure performance testing takes into account  this type of analysis

Source:Jack Vamvas (http://www.sqlserver-dba.com)