How to Create and Use Updateable Views in SQL Server: A Complete Guide

Let's create tblEmployees table and populate it with some sample data.

SQL Script to create tblEmployee table:

<span class="pln">
</span><span class="typ">Drop</span><span class="pln"> table tblEmployee
CREATE TABLE tblEmployee
</span><span class="pun">(</span><span class="pln">
  </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pun">,</span><span class="pln">
  </span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln">
  </span><span class="typ">Salary</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
  </span><span class="typ">Gender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln">
  </span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

Insert data into tblEmployee table

<span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'Azam'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3400</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'Zoe'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">6000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'Inza'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4800</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="str">'Sofia'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3200</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="str">'Samser'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4800</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln">
</span>

Let's create a view, which returns all the columns from the tblEmployees table, except Salary column.

Code:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> view vWEmployeesDataExceptSalary
</span><span class="kwd">as</span><span class="pln">
</span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span>

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.

Code:

<span class="pln">
</span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> vWEmployeesDataExceptSalary
</span>

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 Azam to Ali Azam. Though, we are updating the view, SQL server, correctly updates the base table tblEmployee. To verify, execute, SELECT statement, on tblEmployee table.

Code:

<span class="pln">
</span><span class="typ">Update</span><span class="pln"> vWEmployeesDataExceptSalary 
</span><span class="typ">Set</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Ali Azam'</span><span class="pln"> </span><span class="typ">Where</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pln">
</span>

Along the same lines, it is also possible to insert and delete rows from the base table using views.

<span class="pln">
</span><span class="typ">Delete</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> vWEmployeesDataExceptSalary </span><span class="kwd">where</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pln">
</span>

<span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> vWEmployeesDataExceptSalary values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Roni'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln">
</span>

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.