A table is where data is stored. You always start with tables first, and then your usage pattern dictates whether you need views or materialized views.
A view is like a stored query for future use, if you’re frequently joining or filtering the same tables the same way in multiple places.
A materialized view is like a combination of both: it’s a table that is automatically populated and refreshed via a view. You’d use this if you were using views, and want to pre-join or pre-aggregate the rows to speed up queries.
Figure below shows an example of using the Materialized View pattern. Data in the Order, OrderItem, and Customer tables in separate partitions in a Microsoft Azure storage account are combined to generate a view containing the total sales value for each product in the Electronics category, together with a count of the number of customers who made purchases of each item.
Materialised View:Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data.It provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
Senario:While creating a dataware house we need to go for staging part at that time this is useful.
We have 2 types
An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.
In order to be updatable, a view cannot contain any of the following constructs:
SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list
or finally (with some exceptions) a JOIN
Views that are not updatable can be modified using an INSTEAD OF trigger.