How to create materialized views in SQL Server?
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
Although purely from engineering perspective, indexed views sound like something everybody could use to improve performance but the real life scenario is very different. I have been unsuccessful is using indexed views where I most need them because of too many restrictions on what can be indexed and what cannot.
If you have outer joins in the views, they cannot be used. Also, common table expressions are not allowed... In fact if you have any ordering in subselects or derived tables (such as with partition by clause), you are out of luck too.
That leaves only very simple scenarios to be utilizing indexed views, something in my opinion can be optimized by creating proper indexes on underlying tables anyway.
I will be thrilled to hear some real life scenarios where people have actually used indexed views to their benefit and could not have done without them
You might need a bit more background on what a Materialized View actually is. In Oracle these are an object that consists of a number of elements when you try to build it elsewhere.
An MVIEW is essentially a snapshot of data from another source. Unlike a view the data is not found when you query the view it is stored locally in a form of table. The MVIEW is refreshed using a background procedure that kicks off at regular intervals or when the source data changes. Oracle allows for full or partial refreshes.
In SQL Server, I would use the following to create a basic MVIEW to (complete) refresh regularly.
First, a view. This should be easy for most since views are quite common in any database Next, a table. This should be identical to the view in columns and data. This will store a snapshot of the view data. Then, a procedure that truncates the table, and reloads it based on the current data in the view. Finally, a job that triggers the procedure to start it's work.
Everything else is experimentation.