database design - How to create materialized views in SQL Server?

Question

I am going to design a DW and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with an query example..

Answer 1

They're called indexed views in SQL Server - read these white papers for more background:

• Creating an Indexed View
• Improving Performance with SQL Server 2008 Indexed Views

Basically, all you need to do is:

• create a regular view
• create a clustered index on that view

and you're done!

The tricky part is: the view has to satisfy quite a number of constraints and limitations - those are outlined in the white paper. If you do this, that's all there is. The view is being updated automatically, no maintenance needed.

Additional resources:

• Creating and Optimizing Views in SQL Server
• SQL Server Indexed Views