How to create materialized views in SQL Server?

Question

I am going to design a Data Warehouse 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 a query example?

Solution 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

Solution 2

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

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Deepak]

Thanks for your reply. I got what i want.. I would like to know about indexes as well. I want to know is there any way to generate star schema diagram in SQL server when I have all the table structure ready ? If Yes how do i create fact table for that ?

[ErikE]

Your comments about SQL Server are incorrect--materialized views are very different things in Oracle and SQL Server. In SQL Server, a view with a unique clustered index on it (a.k.a. a "materialized view") does not and cannot be updated by the user, nor is it stored in a separate user-created table--it is always updated by the engine during updates, and is never out of sync. There need be no job to store a snapshot of the data.

[Admin]

What the OP asked for is easily provided by an indexed view. That's the closest thing SQL Server natively provides to an Oracle materialized view. However if you want/need to exactly replicate the way an Oracle MVIEW works, Jason is right. Jason's approach also helps in the same scenario Oracle MVIEWs can - for example doing out of hours refresh of a reporting table where you care more about database load than how up to date the view is (e.g. reporting only on yesterday's numbers...)

[areyesram]

Actually I have used Indexed Views (just once) to partition a Full Text Search index. FTS indexes indeed can't be partitioned, but separate indexes can be created on several views from the same table. It was kind of a last resort, though.

[ajeh]

You need to remember to add (NOEXPAND) hint to the queries that use the indexed views. And then you notice the difference. The advantage of using the indexed views vs "properly indexing the tables" is in limiting the record selection, otherwise you are correct, it would be the same.

[Jeff Wilson]

The restrictions on putting a clustered index on the view are extensive. For example, the view can't reference other views and can't contain outer joins. So, many views that need better performance can't use this method. Still a good answer.

[Suncat2000]

As mentioned in a related question, the MSDN blog article, blogs.msdn.microsoft.com/ssma/2011/06/20/…, highlights some of the key differences between materialized views and indexed views. The most problematic IMHO is not being able to specify refresh triggers: indexed views are updated whenever the base tables are updated - undermining most of the performance benefits of using a materialized view. Prohibitions on joins, aggregates, windowing functions, and subqueries makes indexed views nearly pointless unless data doesn't change often.

[aruno]

Yes the NOEXPAND thing cannot be understated!

[youcantryreachingme]

@Suncat2000 - agree the restrictions and limitations are insane. Almost criminal to call them materialised views. Hang on. Wait. They didn't. I disagree on the performance concern though - overhead on data update is traded-off against faster querying and synchronised results.

[youcantryreachingme]

@ajeh - limited record selection is not the only difference. I think the point is that on your underlying table you may have only one unique clustered index. The view allows you to construct a second unique clustered index against the same underlying data - thus offering the query optimiser different alternatives for retrieving data depending on the query being run. Remember too that the query optimiser may elect to use your indexed view in order to fulfil completely unrelated queries within your system - thus improving performance across the whole system where it benefits.

[youcantryreachingme]

This. Completely relevant, forgotten alternative! Cheers. PS - your second link now routes to the first URL on the MS site anyway.