Materialized view in sql

Can anyone explain me what is the materialized view in sql as well as it’s significance in sql?

Materialized view nowadays called snapshot

In summary, an object with the result of the query. Since it contains the result of a query, I think that its significance in SQL is obvious. But do let me know if you need further explanation :smiley:


Your question said to materialized view in SQL. Which product do you plan on using? Orable? MySQL? SQL Server aka T-SQL? Each one of three relational database that i just mentioned will treat a materialized view slightly different from the others.

The first thing that happens is that just with any other view, SQL Server expands the view definition and optimizes the expanded query. If you have Enterprise Edition or Developer Ediition, as part of the optimization, SQL Server attempts to match the query against existing indexed views, and may decide to use the view instead.

From this follow that SQL Server could decide to use a different indexed view. It also follows that SQL Server could decide to use the view, even if you query only included the base tables.

It also follows from this that if you have Standard or Express Edition, SQL Server will not read from the view directly.

You can, however, override this behaviour. You can specify the hint NOEXPAND with the view:


This instructs SQL Server to not expand the view, and thus with this hint, you are guaranteed that SQL Server always reads from the view.


[quote=“SamGrayson, post:3, topic:113408, full:true”]
freudian slip, there, buddy



Most notably because MySQL doesn’t support matrialized views at all! :stuck_out_tongue:

But there are tricks to do it :wink:

Yes you can use triggers to update a “material view” table, but it really isn’t the same.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.