****POC: Updating the complex View****

Is Complex views are updatable ?

yes,we can as follows :)

Generally we have two types of views are there, simple view(the view which created on single table) and complex view(the view which is created using more than one table and we can consider the view as complex view even if we create on single table if it contains(distinct,aggregate function,group by,having clauses,computed columns and set operators).

So a simple view can be updatable but a complex view can't be updatable because it contains the columns of two or more tables.

But it is not an achievable thing, we can achieve this thing by using Instead of trigger

please find the below tables.

From the above two tables I am creating a view as follows

create view emp_dept
as
select e.eno,e.ename,e.job,e.sal,d.dno,d.dname,d.loc
from employee10 e inner join department d
on e.deptno=d.dno

when ever we are trying to insert a record in the view,we got the below error

"View or function 'emp_dept' is not updatable because the modification affects multiple base tables."

So we can overcome this drawback using instead of triggers as shown in below 

Now the same insertion will success as shown below.

Now 

The new row got inserted in both of the tables.The remaining columns which are not available on view will be filled with null value.

By using this approach we can update the complex view.

Let me know if you have any queries on this.

Thanks
Srinivas
9059361460

Views in SQL SERVER 2012

Views

View is a logical table used to display the required information from one or more tables.View will be created by using select  statement.View will not store values and will not use memory.

It will display records from table/tables.Vew will not have any data of its own.The data it will display will be from the table.

Syntax:

1.To Create a view 

 create view <view_name>
 as
 seelct statement

Example: 
create view view1
 as
 select * from department10

 select * from view1 'We can see all the data that is available on department10


2.To alter a view

Alter view <view_name>
 as
 select statement


3.To see the definition of a view

sp_helptext <View_Name>

 Example:

sp_helptext view1 'we can see the definition of the view1


4.To see list of views in a database:

select * from sysobjects
where xtype='V' 

Benefits of  a view:

1.If we want to provide an access to a subset of data from the table we can create a view with the required data and give permissions on that view so
that we can manage security 

2.When we want to access data from a table or tables by writing a complex queries always in such cases,if we create a views basing on that 
queries we can directly query on the views only without writing Queries every time.

3.whenever a user performs any operation on the view like select /insert/update/delete internally the views perform those operations on a table

Views classification:

1.Simple view :

A view which is created basing on the columns of single table is known as Simple Table.


Example:
 create view view1
 as
 select * from department10

 create view view2
 as
 select enum,ename,dno from Employee10

 select * from view2 'we can see the enum,ename,dno data from Employee10

 alter view view2
 as
 select enum eno,ename employeename ,dno deptno from Employee10 'Chnaging the view definition

 select * from view2


2.complex view

A view which is created basing on multiple tables is a complex view.

Example:
create view view3
as
select e.enum,e.ename,e.sal,d.deptno,d.dname,d.loc
from Employee10 e,department10 d
where e.dno=d.deptno

***Note:

A view which is created basing on a single table will also be considered as a complex view if the query contain any of the following 

distinct,aggregate,function,group by clause,having clause,calculated columns and set operators

3. Updatable  and non updatable views

A view which allows manipulation to the data associated with the view we called it as a updatable view and if it doesn't allow manipulations it is a non updatable view.

Note:

By default all the columns of a simple view are updatatable and complex views are non updatable.

We can update the complex views also but the columns associated with one table.


 update view2
 set employeename='Vinod Kumar'
 where employeename='vinod' 'Updating the employeename from vinod to vinod kumar


 select * from view2 'Updated

 select * from Employee10 'The same update was effected to table


 create view view1
 as
 select * from department10

 select * from view1

 insert into view1 values(50,'admin','Hyderabad') 'Inserted a new record in to view (the same will be inserted to the reference table


 select * from view1 'We can see the inserted record to view

 select * from department10 We can see the inserted record to Table

 delete from view1
 where deptno=50 'Deleting a record from view

 select * from view1

 select * from department10

 update view1
 set deptno=50
 where dname='Accounts' 'updating a record on view

 select * from view1

 select * from department10

Insert/update/delete operations are performed on the view

With Check Option:

If we create a view by using where condition and if we perform some dml operation against that where condition it will accept that operation.

 create view view4
 as
 select * from employee10 where dno=40 'creating view for the employees related to deptno 40

 select * from view4

 insert into view4(enum,ename,dno) 
 values(1011,'kumar',10) 'Inserted a record related to dept 10 on the view which contains dept 40 employees


If we want to restrict any dml operation against that where condition we need to add with check option.

 alter view view4
 as
 select * from employee10 where dno=40
 with check option 'changing the definition of a view by using with check option


 insert into view4(enum,ename,dno) 
 values(1012,'Olu',10) 'in valid throw an exception

 insert into view4(enum,ename,dno) 
 values(1012,'Olu',40) 'Valid


 With Encryption:

After creating the view  we can see the definition of the view by using sp_helptext procedure,if we don't want to give the definition to the users then we can hide it 
by using "With Encryption" option.

sp_helptext view4 'we can see all the definition of a view

  
 alter view view4
 with Encryption
 as
 select * from employee10 where dno=40 'changing the definition of the view to hide the definition


 sp_helptext view4 'It wont show anything,The definition is encrypted 


Example:(with encryption  and with check option

 create view view5
 with Encryption
 as
 select * from employee10 where dno=10
 with check option

 insert into view5(enum,ename,dno) 
 values(1013,'Olu',20)

 sp_helptext view5

With Schema Binding :

Even if we can create based on table we can delete/drop a table with out any restriction,the views will be alive and those are inactive,but if we 

specify with schema binding we can't drop or alter the table columns on which the view is created.


NOTE:

1.whenever we want to use schema binding we need to specify columns separately in select statement we can't use *

2.While using the schema binding option the table name must be prefixed with the owner name dbo.


 delete from Employee10 'deleting the table


 select * from view5 'Views are available but views are empty along with table


 drop table Employee10 'table is dropped

 select * from view5 'views are available but throw an error that referencing invalid object.


 create view view6
 with schemabinding
 as
 select e.enum,e.ename,e.sal from dbo.Employ1000 e where dno=20
'Creating a view for dno 20 employees with schema binding option

 select * from Employ1000

 drop table Employ1000 'we cant delete this table server will throw an error that dependent views are available.


With Encryption and schema binding Example:

 sp_helptext view6

 alter view view6
 with encryption,
 schemabinding
 as
 select e.enum,e.ename,e.sal from dbo.Employ1000 e where dno=20


 sp_helptext view6 'we can't see definintion

 drop table Employ1000 'we can't drop a table

With Encryption,schema binding and check Option Example:


 alter view view6
 with encryption,
 schemabinding
 as
 select e.enum,e.ename,e.sal from dbo.Employ1000 e where dno=20
 with check option 
  
if require we can combinely use all the options.


Thanks
Srinivas

Views in SQl Server

VIEWS

A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table.

There are two types views available in SQL Server.

1.    Simple Views
2.    Complex Views

Note: 

 If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa.

1.Simple Views: Creating View by taking only one single base table.

Syntax:

CREATE VIEW VIEWNAME
 [WITH ENCRYPTION]
[WITH SCHEMABINDING]
AS
SELECT * FROM TABLENAME [WHERE CONDITION]
   [WITH CHECK OPTION]

E.g.:
CREATE VIEW V1 AS SELECT * FROM EMP

INSERT INTO V1 VALUES (55,’RAVI’, 10000,10)

The above insert statement inserts the values into base table EMP as well as into view V1.

E.g.:

CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10

INSERT INTO V2 VALUES (66,’BABBU’, 25000,10)

The above insert statement inserts the values into base table EMP as well as into view V2.

INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20)

The above insert statement inserts the values into only base table EMP but not into view V2 because according to the definition of V2 user supplied values are invalid values.

It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’.

E.g.:
CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10
        WITH CHECK OPTION

INSERT INTO V3 VALUES (88,’TEJA’, 25000,20)
The above insert statement cannot inserts the values into base table EMP as well as into view V3.

SP_HELPTEXT: This stored procedure is used to display the definition of a specific view.

Syntax:   SP_HELPTEXT    VIEWNAME
E.g.: SP_HELPTEXT      V1

Output: CREATE VIEW V1 AS SELECT * FROM EMP

WITH ENCRYPTION: Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition.

E.g.:
CREATE VIEW V4
WITH ENCRYPTION
AS
SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

Output :The text for object v4 is encrypted

To decrypt the definition of view V4 we have to follow the below approach

1.    Replace CREATE with ALTER
2.    Remove WITH ENCRYPTION keyword
3.    Select the query and press F5.

E.g.:
ALTER VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

CREATE VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

2.Complex Views:

Creating View by taking multiple base tables.

Ex:
CREATE VIEW EMP_DEPT_VIEW
AS
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

Syntax To Create view based on another views:
SQL SERVER enables users to create views based on another view.We can create view based on another view up to 32 levels.

Syntax:

CREATE VIEW VIEWNAME
[WITH ENCRYPTION]
[with scehmabinding]
AS
SELECT * FROM VIEWNAME [WHERE CONDITION]
   [WITH CHECK OPTION]

E.g.: CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10

Syntax To Drop the Views:
DROP VIEW VIEWNAME […N]

E.g.: DROP VIEW V1, V2, V3, V4, V5

Schema Binding :

a. when view created by schema Binding then view becomes dependent object on base table.

b. The base table cannot be dropped without dropping view.

c. When schemabinding is used then tablename prefixed by username/schemabname.

ex:

create view v8
with schemabinding
as
select empno,ename,sal,job from dbo.emp;

ex: drop table emp

    Invalid , because view is dependent object so we first drop the view after that table.

Advantages of view:

1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.

2. To control access to rows and columns of data.

Thanks
GSV