Views in real time senario

Views are nothing but saved select query. Views nothing but virtual table on top of physical table. Views can contain rows and columns .views is not stored data. The main use of view is hiding some rows data or some columns.

There are two types of views:

1. System defined view.
2. User defined view.

System Defined view:

System defined view is categorized into 3 types

System defined views can discuses in future articles.

User defined views

There are two types of user defined views.

Simple View:

A simple view is nothing but just single saved select statement.

create table Emp

(

ID int identity(1,1),

Name varchar(20),

Department varchar(20)

)

insert into Emp(Name,Department) values('rakesh','software'),('raju','bpo'),('ali','software')

I have created “Emp” table with above following data.

select * from Emp

When we run select query on top of Emp table data should be like this

When we run select query on top of Emp table data should be like this

When we observe above data in Emp table have 3 records exists. In my company there are 2 departments is there one is “Software” and another one “Bpo”. In software development manager asks the Software development team details. if I give entire Emp table to Software development manager they have chance to check “Bpo” team information also ,but this is not privacy . in this senario we need to create view on the “Emp” table .

Syntax:

Create view ViewName

As

Select Query

Example 1

In this example we are creating the view in row – level security.

create view V_EmpSoftware

As

select * from Emp where Department='software'

select * from V_EmpSoftware

Example 2

In this example we are creating view in column – level security . We are hiding the ID column.

create view V_EmpSoftware_ID

As

select Name,Department from Emp

select * from V_EmpSoftware_ID

Is it possible insert , update , Delete operations on View?

Yes

Example

insert into V_EmpSoftware (Name,Department) values('Vaenkatesh','software')

select * from V_EmpSoftware

whenever we are trying to perform insert , update , delete operations on view base tables also effected.

We can check base table also

select * from Emp

Complex Views:

Complex view are nothing but multiple select statements saved query .

Example

create table SchoolName(

ID int identity(1,1) primary key,

Name varchar(20) unique

)

create table Student(

ID int identity(1,1) primary key,

Name varchar(20) unique ,

Marks int not null check (marks>500),

SchoolName int foreign key references SchoolName(ID)

)

insert into SchoolName

select 'HydPublic'

union all

select 'BabyMoon'

insert into Student(Name,Marks,SchoolName) values('rakesh',550,1)

insert into Student(Name,Marks,SchoolName) values('phani',550,2)

Here we demonstrating the creating the complex view by using above base tables.

create view V_Student_SchooName

As

select S.Name,S.Marks,SN.Name As [SchoolName] from Student S Join SchoolName SN

on s.SchoolName=SN.ID

select query from Complex View.

select * from V_Student_SchooName

DML Operations on Complex views.

insert into V_Student_SchooName(Name,Marks,SchoolName)

select 'Madhu',500,'Delhi pub'

whenever we trying insert the data into base tables by using complex view it will give error. Because of ,

Msg 4405, Level 16, State 1, Line 1

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

How can we insert data using complex view ?

Ans:

By Using creating the Instead of trigger on top of view.

create trigger Trg_V_Student_SchooName on V_Student_SchooName

instead of insert

as

begin

insert into SchoolName

select SchoolName

from inserted

end

After creating the trigger try to insert data using complex view

insert into V_Student_SchooName(Name,Marks,SchoolName)

select 'Madhu',500,'Delhi pub'

Check the data

select * from SchoolName

How to update the meta data of view ?

Example

create view v_SchoolName

as

select * from SchoolName

we are created the simple view on SchoolName table. We are selecting all the columns from base table in a view .

select * from v_SchoolName

alter table SchoolName add address varchar(500)

after that added address column to SchoolName table.

select * from v_SchoolName

if we run select query on top v_SchoolName view the added column is not show.

If we want that meta data of base table columns we need to Refresh the view.

sp_refreshview is a system stored procedure.

exec sp_refreshview v_SchoolName

after that we run select query on top of view.

select * from v_SchoolName

Observe the result. Address column also selected.

Limitations:

1. you can not pass parameter to view.
2. Views can not create on temporary tables.
3. After trigger can not created on view.

Write the answers in Comment box with question no .

• What are types of views ?
• What are types of user defined views ?
• Can we preform insert , update , delete operation on top of complex view . Please explain scenario.
• How to update meta data of view . Which command is used .?