What are the advantages and disadvantages of View in SQL?

A view is a permanently stored and named SQL query in the database. A view is a virtual table in the database whose contents are defined by a query. The rows and columns of data visible through the view are the query results produced by the query that defines the view.

To the users, the view appears just like a real table with a set of named columns and rows of data – one can query a view as if one is querying a table. But unlike a real table, a view does not exist in the database as a stored set of data values.

Advantages of View

1. Security

Every user can be given permission to access the database only through a small set of views that contain specific data the user is authorized to see. Thus, the user’s access to stored data is restricted.

2. Query Simplicity

A view can draw data from a number of tables or through a complex query and present it as a single table, thus turning such complex queries into single table query against the view.

3. Structural Simplicity

Views can give the users a personalized view of the database structure, thus presenting the database as a set of virtual tables.

4. Insulation from change

A view can present a consistent, unchanged image of the structure of the table, even if the underlying tables are split, restructured or renamed.

5. Data Integrity

If the data is updated or entered through a view, the DBMS can check the data to ensure that it meets integrity constraints of the underlying table.

Disadvantages of View

1. Performance

Whenever a view is queried, the DBMS translates the queries against the view into queries against the underlying tables. If the view is defined by a complex query, then even a simple query against the view becomes a complex query and it takes a long time to get executed.

2. Update restrictions

A view can be updated only if certain conditions hold true on the query that forms the view. Such conditions are true only for simple views, thus complex views cannot be updated.