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