Views In Microsoft SQL Server

A View is a logical or virtual table, which does not exist physically in the database. A view is an object which contains a 'select' statement. The view is considered as a Virtual table.
We create a view for two purposes.

1. Security region --> If we don't want to show all the column's data of the table to a user then we generally create a view.
2. To make complex queries simple.

We will see examples

Create a view with two tables:- we create a view with the help of the joining two tables.

    create view vdata(student_id,student_name,student_course)

as

select sid,sname,cname from scourse c  join studentdetails s

on c.id=s.sid

Output:-

Create a view with Left Outer Join:-

Create View Vdata1(Student_id,Student_name,Student_course)

As

Select Sid,Sname,Cname From Scourse C  Left Join Studentdetails S

On C.Id=S.Sid

Output:-

Create View With Right Outer Join:-

Create View Vdata2(Student_id,Student_name,Student_course)

As

Select Sid,Sname,Cname From Scourse C  Right Join Studentdetails S

On C.Id=S.S

Output:-

Create View With Full Outer Join:-

Create View Vdata3(Student_id,Student_name,Student_course)

As

Select Sid,Sname,Cname From Scourse C  Full Outer Join

Studentdetails S On C.Id=S.Sidid

Output:-

To Get The Data Through View:-
Syntax:-

Select *From View_name

Ex.

Select*From Vdata

To Get The Details Of View:-

Sp_help Vdata

Sp_helptext Vdata

Output:-

Drop View And View Name:-

Drop View Vdata

Insert, Delete, Update Records Within A Table Through View:-

We Can Insert, Delete, Update The Records Within A Table Through A View, But Only Two Conditions.

1. The view must contain only one table within the select query(no join tables).
2. Select query in view must contain all not nullable columns.

There Are Some Steps To Understand The Whole Concepts:-

Step1
First Create A Studentdetails Table As Above Shown-->Create Constraint Not Nullable Column If  Table Is Already Created As Given Below:

Alter Table Studentdetails Alter Column Sage Int Not Null

Step2
 Now Create View With All Not Nullable Column Otherwise You Can Not Insert Data Through View.

Create View Myview1

As

Select Sid,Sname,Sage From Studentdetails

Step3
Insert Data In Studentdetails Table Through Myview1.-->You Can Easily Insert And Update The Table Through Myview1 Because You Have Selected All Not Nullable Column.

Insert Into Myview1 Values(109,'Kajal',21)

Output:-

Step4

Update Values In Studentdetails Tables Through Myview1.

Update  Myview1 Set Sage=50 

Where Sid=106

Output:-

Step5

 Now I Am Going To Create A New View (Myview2) As Given Below:-

Create View Myview2

As

Select Sid,Sname From Studentdetails 

Step6

Now Insert Values In Studentdetails Table Through Myview2,Then It Will Give Error As Shown Below.

Insert Into Myview2 Values(110,'Karan',21)

output

Note:- You Can Insert And Update Records In Tables Through View When Table Has No Constraints(Not Nullable).If Any Column Is Not Nullable The You Have To Follow The Above Steps Otherwise You Can Not Insert And Update The Records Through View.