Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Updateable Views - Part 40

In Part 39, we have discussed the basics of views. In this session we will learn about Updateable Views. Let's create tblEmployees table and populate it with some sample data.

Create Table tblEmployee Script:
CREATE TABLE tblEmployee
Id int Primary Key,
Name nvarchar(30),
Salary int,
Gender nvarchar(10),
DepartmentId int

Script to insert data:
Insert into tblEmployee values (1,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)

Let's create a view, which returns all the columns from the tblEmployees table, except Salary column.
Create view vWEmployeesDataExceptSalary
Select Id, Name, Gender, DepartmentId
from tblEmployee

Select data from the view: A view does not store any data. So, when this query is executed, the database engine actually retrieves data, from the underlying tblEmployee base table.
Select * from vWEmployeesDataExceptSalary

Is it possible to Insert, Update and delete rows, from the underlying tblEmployees table, using view vWEmployeesDataExceptSalary?
Yes, SQL server views are updateable.

The following query updates, Name column from Mike to Mikey. Though, we are updating the view, SQL server, correctly updates the base table tblEmployee. To verify, execute, SELECT statement, on tblEmployee table.
Update vWEmployeesDataExceptSalary 
Set Name = 'Mikey' Where Id = 2

Along the same lines, it is also possible to insert and delete rows from the base table using views.
Delete from vWEmployeesDataExceptSalary where Id = 2
Insert into vWEmployeesDataExceptSalary values (2, 'Mikey', 'Male', 2)

Now, let us see, what happens if our view is based on multiple base tables. For this purpose, let's create tblDepartment table and populate with some sample data.
SQL Script to create tblDepartment table 
CREATE TABLE tblDepartment
DeptId int Primary Key,
DeptName nvarchar(20)

Insert data into tblDepartment table
Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')

Create a view which joins tblEmployee and tblDepartment tables, and return the result as shown below.

View that joins tblEmployee and tblDepartment
Create view vwEmployeeDetailsByDepartment
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId

Select Data from view vwEmployeeDetailsByDepartment
Select * from vwEmployeeDetailsByDepartment

vwEmployeeDetailsByDepartment Data:

Now, let's update, John's department, from HR to IT. At the moment, there are 2 employees (Ben, and John) in the HR department.
Update vwEmployeeDetailsByDepartment 
set DeptName='IT' where Name = 'John'

Now, Select data from the view vwEmployeeDetailsByDepartment:

Notice, that Ben's department is also changed to IT. To understand the reasons for incorrect UPDATE, select Data from tblDepartment and tblEmployee base tables.

tblEmployee Table


The UPDATE statement, updated DeptName from HR to IT in tblDepartment table, instead of upadting DepartmentId column in tblEmployee table. So, the conclusion - If a view is based on multiple tables, and if you update the view, it may not update the underlying base tables correctly. To correctly update a view, that is based on multiple table, INSTEAD OF triggers are used.

We will discuss about triggers and correctly updating a view that is based on multiple tables, in a later video session.


  1. Sir, I an great fan of your videos. I have learnt so many things from it.
    I am very much thankful to you.
    Could you please explain what will happen when updating a computed column(e.g. calculated,decrypted)
    in a view?

  2. An Update statement with View can effect on table data??

  3. Sir i like your videos, i am following you..i have some doubt ,is it possible update ,delete ,insert on views it contains more tables? incase there is a chance how to do that? another one ..if changed columns data on views it getting effecting on base table so it is good or bad? if bad how to prevent that one.. another one .. i created view but some one changed my original table then view was effected so how to stop others didn't change base table data until my permissions...

    1. You will have to give the permissions to the tables to not update by the others.

  4. Hi Vitalreddy,
    Venkat already explained this for functions.
    same can be applied here for view as well. I mean schema binding.

    alter view vWEmployeesByDepartment
    with schemabinding

    select Id, Name, Salary, Gender, DeptName from dbo.tblEmployee E

    join dbo.tblDepartment D

    on D.DeptId = e.DepartmentId

    when you use shcemabinding your objects must containe 2 part names like dbo.tblEmployee


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.