How to create materialized views in SQL Server?

sql-server database-design indexed-views
Deepak picture Deepak · Oct 21, 2010 · Viewed 226.6k times · Source

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

marc_s picture marc_s · Oct 21, 2010

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

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:

Related questions

Difference between clustered and nonclustered index

I need to add proper index to my tables and need some help. I'm confused and need to clarify a few points: Should I use index for non-int columns? Why/why not I've read a lot about clustered and non-clustered …

Read More
"Prevent saving changes that require the table to be re-created" negative effects

Preamble I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2). I got the error "The changes you have made require the following tables to be dropped and re-created" from SQL Server. Before …

Read More
What are the best practices for using a GUID as a primary key, specifically regarding performance?

I have an application that uses GUID as the Primary Key in almost all tables and I have read that there are issues about performance when using GUID as Primary Key. Honestly, I haven't seen any problem, but I'm about …

Read More