View

/*Table Creations-Start*/
DROP TABLE TEMP_EMPLOYEE
DROP VIEW EMPLOYEE_SIMPLE_VIEW
DROP VIEW EMPLOYEE_COMPLEX_VIEW

CREATE TABLE TEMP_EMPLOYEE
(
Emp_No INT,
Emp_Name VARCHAR(100)
)

INSERT INTO TEMP_EMPLOYEE
SELECT 100,'MOOV'

INSERT INTO TEMP_EMPLOYEE
SELECT 101,'ANAND'

DROP TABLE TEMP_EMPLOYEE_SALARY

CREATE TABLE TEMP_EMPLOYEE_SALARY
(
Emp_No INT,
SALARY MONEY,
Salary_Month varchar(10)
)

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 100,10000,'Jan'

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 101,15000,'Jan'

SELECT * FROM TEMP_EMPLOYEE

SELECT * FROM TEMP_EMPLOYEE_SALARY

/*Table Creations-End*/

/*View Creations-Start*/

/*View are virtual table, whose content is defined by queries*/

/*View with one table*/
CREATE VIEW EMPLOYEE_SIMPLE_VIEW
AS
SELECT Emp_No , Emp_Name FROM TEMP_EMPLOYEE

SELECT * FROM EMPLOYEE_SIMPLE_VIEW

/*View with More than one table*/
CREATE VIEW EMPLOYEE_COMPLEX_VIEW
AS
SELECT X.Emp_No , Emp_Name,SALARY,Salary_Month FROM TEMP_EMPLOYEE X
INNER JOIN TEMP_EMPLOYEE_SALARY Y ON X.Emp_No = Y.Emp_No

SELECT * FROM EMPLOYEE_COMPLEX_VIEW

/*View Creations-End*/

/*Inserting Record into Simple View*/

insert into EMPLOYEE_SIMPLE_VIEW
select 103,'vicky'

/*Inserting Record into Complex View*/

select * from EMPLOYEE_COMPLEX_VIEW

/*This quey will throw error. Because, usage of multible tables in single view.. */
insert into EMPLOYEE_COMPLEX_VIEW
select 104,'KC'

/*Now Specify the columns to insert*/
insert into EMPLOYEE_COMPLEX_VIEW (Emp_No , Emp_Name)
select 104,'KC'


/*Delete Record from Simple View*/
DELETE FROM EMPLOYEE_SIMPLE_VIEW where Emp_Name = 'BS'

/*Delete Record from Complex View*/
DELETE FROM EMPLOYEE_COMPLEX_VIEW where Emp_Name = 'Vicky'
/*Usage of multible tables, we cant decide which table rows can be deleted!*/


/*Update Record in Simple View*/
update EMPLOYEE_SIMPLE_VIEW
set Emp_Name = 'moovee'
where Emp_No = 100

select * from EMPLOYEE_SIMPLE_VIEW

/*Update Record in Complex View*/
update EMPLOYEE_COMPLEX_VIEW
set Emp_Name = 'mooventhan'
where Emp_No = 100
/*It Updates*/

update EMPLOYEE_COMPLEX_VIEW
set salary = 45000
where Emp_No = 100
/*It Updates*/

/*Now Insert Feb Salary for Mooventhan*/

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 100,10000,'Feb'

/*Now update salary*/
update EMPLOYEE_COMPLEX_VIEW
set salary = 100000
where Emp_No = 100

SELECT * FROM EMPLOYEE_COMPLEX_VIEW
/*It updates both Jan and Feb Salary. So need to check View query and conditions before update view.*/


/*So, View are not only for Reading Data... We can Insert records into view.. But there is some condition. Here our records inserted successfully but not always.
Consider, if some colums not selected in viewes are not null columns.. What will happen? It will throw error..
*/


/*You can Drop table , even when those tables are used in views*/

begin tran

drop table TEMP_EMPLOYEE

select * from EMPLOYEE_SIMPLE_VIEW

/*Now view is orphaned. We cant use this view no more*/

rollback

/*How can we avoid this...*/

/*Lets alter this view with schema binding option*/
ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING
AS
SELECT Emp_No , Emp_Name FROM TEMP_EMPLOYEE

/*It Throws error..  Because Table Names must be in two-part format and an object cannot reference itself*/

/*Now Try this dbo.TableNAme*/

ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING
AS
SELECT Emp_No , Emp_Name FROM dbo.TEMP_EMPLOYEE


/*Now Try to drop table , even when those tables are used in views*/

begin tran

drop table TEMP_EMPLOYEE
/*It Throws error. We cant delete it until this table removed from view!*/

rollback


sp_helptext EMPLOYEE_SIMPLE_VIEW

ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING, ENCRYPTION
AS
SELECT Emp_No , Emp_Name FROM dbo.TEMP_EMPLOYEE


sp_helptext EMPLOYEE_SIMPLE_VIEW
/*The text for object 'EMPLOYEE_SIMPLE_VIEW' is encrypted.
So now you cant view the query used in views
*/


/*
Avoides Complexity
Increases security
Viiew is slow when compared to Actual query. But, We can create Index on View when schema binding option is ON.
*/

/*
Advanced Topics in View,

Trigger on View,
Indexed View,
Partioned View,

*/