Difference Between View and Materialized View in SQL

In this post i am sharing difference between view and Managerial view in database or SQL. This is one of the popular interview question,it doesn't matter whether you are programmer,DBA,or backend developer. These SQL questions common for all. Now let us see what is view and materialized view.

View:

Views are virtual table only and run the query definition each time they are accessed. A view is also useful to hide sensitive data columns.The result is not stored any where in the disk and every time we need to fire the query when we need data.


Materialized view:

Materialized views are disk based and updated manually or using triggers, also the definition of the query will also store in the database.


Now let us see The key differences between View and Materialized view in SQL or Database:

• The view query result is never stored in the disk or database it is only displayed but Materialized view allow the  query result on the disk.
• .In case of view we always get latest data since view is updated each time the virtual table is used but Materialized view we need to refresh the view for getting latest data, Since it has to be updated manually.
• Performance of view is less but Materialized view is fast processing.
• view do not require memory space but Materialized view utilizes memory space.
• No separate copy of table but in case of Materialized view we get physically separate copy of table

All most all applications is used views,we use views because they are more feasible, only logical representation of table data no extra space needed.Materialized views are used mostly with data warehousing or business intelligence application.