SQL Server Materialized Views

27 January,2011 by Tom Collins

Question: Do Materialized views exist in SQL server ? and if so, how are they implemented and maintained for decent performance.

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

It is worth 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.

Materialized Views 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 index 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. Read more on SQL Indexed View limitations

Ensure performance testing takes into account  this type of analysis

Read More

SQL Server Rapid Tuning - SQL Server DBA

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Materialized Views


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer