Difference between View and Materialized View
FREE
What is a View in SQL?
View behaves like a virtual table, it is a virtual relation that acts as an actual relation. It consists of a SELECT statement that is stored as an object in the database. The table referenced in the SELECT statement is called a base table for views. It is not a part of the logical relational model and is used for creating custom virtual relations as per the need of a specific user. There is no limit to creating views. The views tuple are not stored in the database system and are generated every time a view is accessed.
Use of Views
- Data security- It can be used as a security method by letting only authorized users access the view. The access given by the database administrator can be to edit, delete or view the tables inside the view.
- Simplified queries- It can be used to hide the complexity of a SELECT statement.
- Save storage - since views are virtual, they don't occupy storage.
What is a Materialized View in SQL?
The views stored in database are referred to as materialized view in SQL. These are the results of the normal view that is stored in the database. Some database system provides a custom extension for using materialized view, however, there is no standard way of defining the materialized view. View maintenance is called a process for keeping the materialized view updated.
Use of Materialized Views
- Used when views are accessed frequently
- Save computation time as these are stored in the database
- Useful in cases where the relation on which the view is defined is large and the result view is small.
What is the Difference Between View and Materialized View?
| Views | Materialized view |
|---|---|
| Query expressions are stored in a database instead of the resulting tuple of a query expression. | The resultant tuple of the query expression is stored in the database. |
| View does not need to be updated every time the relation is updated. | These are updated as and when tuples are updated. |
| There is no large storage cost associated with the view. | Large storage costs are associated with materialized view. |
| There is no updation cost associated with the views. | Updation cost is associated with the materialized view. |
| For defining a view, SQL standards are used. | For defining materialized view there are no SQL standards used instead functionalities are provided by other databases as an extension within the system. |
| It is useful when the tables in the view are not accessed frequently. | It is used when the view is accessed frequently as it saves computational power by storing the results of views in the database. |
Example of a Materialized View in SQL
Syntax
Example
Creating a materialized view:
Output:
Learn more
Conclusion
- View behaves like a virtual table, it is a virtual relation that acts as an actual relation. It consists of a SELECT statement that is stored as an object in the database.
- Materialized view are the results of the view that is stored in the database. Some database system provides a custom extension for using materialized view, however, there is no standard way of defining it.