Views In SQL Server

Views in SQL Server

A view is a subset of a table and is very much equal to a table and does not contain any memory that's why it is called "Virtual Table".

The main difference between a Table and Views are-

There are mainly 2 types of view -

• Simple view
• Complex View

Simple View - A view that is created using simple select statement is called a simple view.

 

Complex View - It is created by using select statement containing orderby, group by and joins.

As we have mentioned a view is an interface between end-user and the original table.

Simple View Example:

create view myview
as
select EmpId,Name,ContactNo from tbl_empdetails

select * from myview

 

Complex View

Example:

create view mycomplexview
as
select e.EmpId,e.Name,e.ContactNo,l.Location from tbl_empdetails e inner join tbl_Location l on e.EmpId=l.EmpId
select * from mycomplexview

So here, I have created a complex view by joining 2 tables that are the following:

tbl_empdetails  and  tbl_Location.

Here are the two tables,

 

This is tbl_empdetails.

 

This is  tbl_Location and here is my view. 

select * from mycomplexview

 

So in this way we can create complex view as well as simple view on any table.

Now the main question that is raised in an interview is that can a view affect a table?

Means when we insert and update a view can it have any direct effect on the table.

The answer is yes.

Here I have explained how it actually affect the table.

 

 

Now this will affect its table as follows.

 

Thus this clearly indicate that updating the simple view has direct effect on the table.

Now, a complex view is the collection of columns from 2 or more table. In this case is it possible to update the complex view?

Answer- Yes we can update the complex view using number of ways. Here I am explaining one by one.

 

Now updating my complex view as follows.

 

 
Thus it clearly shows that the main table is updated also.