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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to create materialized views in SQL Server?

Examples related to sql-server

Examples related to database-design

Examples related to indexed-views