Views in Microsoft SQL Server 2008

A View is a logical or virtual table,which does not exists physically in the database.View is an object which contains 'select' statement.View is consider like as Virtual table.
We create a view for two purpose.

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

I have already created the tables studentdetails and scourse in joins in sql server. I will explain view with the help of these tables.

Create view with two tables:- I am going to create a view with the help of joining of 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 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.sid
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.sid
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. View must contain only one table within 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)

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.
For More:-

1. Default constraints and check constraints
2. solve sql server problems
3. Multithreading in c#
4. create captcha image
5. File handling real application
6. Interview questions and Answers
7. Send mail from asp.net application free

To Get the Latest  Free Updates Subscribe