What is difference between View and Materialized View in Database or SQL?

What is Materialized View in database

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

A Materialized View (MV) replaces a SQL multi-table-view (or query) with a new table that holds all data permutations
MV's are used to improve performance, and are preferable to replication where problem is due to an inefficient query plan 

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

That’s all on difference between View and materialized View in database or SQL. I suggest always prepare this question in good detail and if you can get some hands on practice like creating Views, getting data from Views then try that as well.

CREATE VIEW vStateCity AS
   SELECT StateID, StateName, CityID, CityName
      FROM tState s, tCity c
      WHERE c.StateIDFK = s.StateID;

Example: MV Create: 

/* Create the MV table */
CREATE TABLE mvStateCity AS SELECT * FROM vStateCity;

/* Optionally add index(es) for the queries you want to speed up */
CREATE INDEX iStateCity ON mvStateCity(StateName, CityName)

/* Rename the old view to save it and to avoid application re-coding */
/* (For MS-SQL, use syntax 'EXEC sp_rename [old], [new]') */
RENAME TABLE vStateCity TO vStateCityOld;

/* Create the view that points to the MV */
CREATE VIEW vStateCity AS
   SELECT * FROM mvStateCity;

Example: MV Query Run Time 

SELECT * FROM vStateCity WHERE STATE= ‘California’ AND CityName LIKE ‘%an%’; 

-----> runs 10x faster than original VStateCity query for large amounts of data