-> A view is a virtual table or stored query.
-> View will never contain data unless it is an indexed view.
-> A view may have some table referenced in the select list or even a view can reference another view as well.
When to declare a view in your project :
-> When you have groups of users for your application and you want to give different set of columns exposed to
different users from a table without giving direct access to the tables.
-> In your project if you are writing a complex join repeatedly crate a view instead that you no need to specify the join
-> For giving backward compatibility interface even in case of schema changes on the original table.
-> By using views we can provide security to the database tables.
Select Name,BirthDate, Salary, Department, BuildingName from Employee;
Assume this query is coming to the database from the Dotnet application.
Note: In the above data base assume some one normalizes the table by splitting it into 2 tables. Then .net
application code will break. Still we can able to give backward compatibility without breaking app code.
Select view Employee as select Name, BirthDate, Salary, DeptId, DeptName, BuildingName from employees2 e2,Department d where e.DeptId=d.DeptId
By using updatable views we can modify underlying tables data.
-> In view we can write only one select statement.
create view <view name>
-> By using view, we can do update,insert,delete and also select.
create view vemployees
select * from employees
insert into vemployees values(9.'vinny','pramada',24,39000,'db',cuurent_timestamp)
select * from vemployees
Query: Write a query for deleting 5th employee using view.
delete employees where eid=5
By using non-updatable views we are not allowed to modify data present in underlying tables.
-> A view will become non-updatable view in the following 3 conditions
* When the select list of view contains distinct keyword.
* If the select list contains computed columns(ex:getdate(),max(col1...) etc) the view ill become non-updatable view.
* If the views select statement contains group by(0) having statement then view becomes non-updatable view.
Ex:create view employees1
select distinct * from employees
select * from vemployees1
delete vemployees1 where eid=1--->this statement will fail since view select statement is having distinct keyword.