Back to SQL Course

A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.

Now let's create a view, using the JOINS query, we have just written.

Create View vWEmployeesByDepartment
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId;

To select data from the view, SELECT statement can be used the way, we use it with a table.

SELECT * from vWEmployeesByDepartment;

When this query is executed, the database engine actually retrieves the data from the underlying base tables, tblEmployees and tblDepartments. The View itself, doesnot store any data by default. However, we can change this default behaviour, which we will talk about in a later session. So, this is the reason, a view is considered, as just, a stored query or a virtual table.

What is a materialized view?

Materialized views is also a view but are disk based. Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.

What are the advantages of Views?

• Views doesn't store data in a physical location.
• View can be use to hide some of the columns from the table
• Views can provide Access Restriction, since data insertion, update and deletion is not possible on the view.

What are the disadvantages of Views?

• When a table is dropped, associated view become irrelevant.
• Since view are created when a query requesting data from view is triggered, its bit slow
• When views are created for large tables, it occupy more memory .