Wednesday, 11 November 2015

Types of View in SQL

A view is an object which is similar to a table. It is known as a "Logical table" because it will never occupy space in the memory for storing the data. It is a dependent object that can be created from an existing table. View allows you to perform query as well as DML operations.
There are four types of views in SQL.
types of view
Types of View
  1. Updatable view: It is a view which allows manipulation on it.
  2. Non-updatable view: It doesn’t allow manipulation on it.
  3. Simple view: It is a view on single table that contains only the column or columns of the table in it.
By default simple views are updatable only insert operation will be restricted if at all not NULL column of the base table were not present in the view.
  1. Complex view: It is a view based on more than one table. Complex views based on multiple tables were referred as non-updatable because here the DML operation can’t affect multiple base table but using a complex view you can perform DML operation referring to a single table. 
A complex view allows manipulation on it but with restriction.
A view based on single table can also be called as complex view if it contains any of the following in it.
  • Group by clause
  • Having clause
  • Distinct
  • Computed columns
What does a view stores in it?
The view only stores the select statement that is used for creating it. It never stores any data in it so view is also known as stored select statement or an imaginary table.
Whenever you query on a view, the view will internal execute the select statement what it has stored and retrieves data from base table which is presented to the end user.
Sometimes a view also allows you to perform changes on it.
The changes that are performed on view will be internally performed by the view on the table on which it was created.

No comments:

Post a Comment