Views:

-> 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 condition repeatedly. -> 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. Ex: Select Name,BirthDate, Salary, Department, BuildingName from Employee; Assume this query is coming to the database from the Dotnet application. view example 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. employee view Select view Employee as select Name, BirthDate, Salary, DeptId, DeptName, BuildingName from employees2 e2,Department d where e.DeptId=d.DeptId

Updatable views

By using updatable views we can modify underlying tables data. -> In view we can write only one select statement. Syntax: create view <view name> as select statement: -> By using view, we can do update,insert,delete and also select. Ex: create view vemployees as 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

Non-updatable views:

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 as select distinct * from employees select * from vemployees1 delete vemployees1 where eid=1--->this statement will fail since view select statement is having distinct keyword.