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-

table

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:
  1. create view myview
  2. as
  3. select EmpId,Name,ContactNo from tbl_empdetails
  5. select * from myview
Complex View

Example:
  1. create view mycomplexview
  2. as
  3. select e.EmpId,e.Name,e.ContactNo,l.Location from tbl_empdetails e inner join tbl_Location l on e.EmpId=l.EmpId
  4. 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.
  1. 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.