How to create materialized views in SQL Server?

Question

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.

When indexed view is not an option, and quick updates are not necessary, you can create a hack cache table:

select * into cachetablename from myviewname
alter table cachetablename add primary key (columns)
-- OR alter table cachetablename add rid bigint identity primary key
create index...

then sp_rename view/table or change any queries or other views that reference it to point to the cache table.

schedule daily/nightly/weekly/whatnot refresh like

begin transaction
truncate table cachetablename
insert into cachetablename select * from viewname
commit transaction

NB: this will eat space, also in your tx logs. Best used for small datasets that are slow to compute. Maybe refactor to eliminate "easy but large" columns first into an outer view.

For MS T-SQL Server, I suggest looking into creating an index with the "include" statement. Uniqueness is not required, neither is the physical sorting of data associated with a clustered index. The "Index ... Include ()" creates a separate physical data storage automatically maintained by the system. It is conceptually very similar to an Oracle Materialized View.

https://msdn.microsoft.com/en-us/library/ms190806.aspx

https://technet.microsoft.com/en-us/library/ms189607(v=sql.105).aspx