Advantages of Using Views in SQL Server: A Comprehensive Guide

What is a View?

A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.

Let's understand advantages of views with an example. We will base all our examples on tblEmployee and tblDepartment tables. 

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>

SQL Script to create tblDepartment table:

<span class="pln">
</span><span class="typ">Drop</span><span class="pln"> table tblDepartment
CREATE TABLE tblDepartment
</span><span class="pun">(</span><span class="pln">
 </span><span class="typ">DeptId</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">DeptName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

Insert data into tblDepartment table

<span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'IT'</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"> tblDepartment values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'Payroll'</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"> tblDepartment values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'HR'</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"> tblDepartment values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'Admin'</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>

At this point Employees and Departments table should look like this.

Now, let's write a Query which returns the output as shown below:

To get the expected output, we need to join tblEmployees table with tblDepartments table.

Join:

<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">Salary</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">DeptName</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span><span class="kwd">join</span><span class="pln"> tblDepartment
on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln">
</span>

Now let's create a view, using the JOINS query, we have just written.

<span class="pln">

</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> vWEmployeesByDepartment
</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">Salary</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">DeptName</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span><span class="kwd">join</span><span class="pln"> tblDepartment
on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln">
</span>

To select data from the view, SELECT statement can be used the way, we use it with a table.

<span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> vWEmployeesByDepartment
</span>

When this query is executed, the database engine actually retrieves the data from the underlying base tables, tblEmployees and tblDepartments. The View itself, doesnot store any data by default. However, we can change this default behaviour, which we will talk about in a later session. So, this is the reason, a view is considered, as just, a stored query or a virtual table.

Advantages of using views:

1. Views can be used to reduce the complexity of the database schema, for non IT users. The sample view, vWEmployeesByDepartment, hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.

2. Views can be used as a mechanism to implement row and column level security.

Row Level Security:
For example, I want an end user, to have access only to IT Department employees. If I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employees. To achieve this, I can create a view, which returns only IT Department employees, and grant the user access to the view and not to the underlying table.

View that returns only IT department employees:

<span class="pln">

</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> vWITDepartment_Employees
</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">Salary</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">DeptName</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span><span class="kwd">join</span><span class="pln"> tblDepartment
on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln">
</span><span class="kwd">where</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'IT'</span><span class="pln">
</span>

Column Level Security:
Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to this views, rather than the base tables.

View that returns all columns except Salary column:

<span class="pln">

</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> vWEmployeesNonConfidentialData
</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">DeptName</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span><span class="kwd">join</span><span class="pln"> tblDepartment
on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln">
</span>

3. Views can be used to present only aggregated data and hide detailed data.

View that returns summarized data, Total number of employees by Department.

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> vWEmployeesCountByDepartment
</span><span class="kwd">as</span><span class="pln">
</span><span class="typ">Select</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(</span><span class="typ">Id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">TotalEmployees</span><span class="pln">
</span><span class="kwd">from</span><span class="pln"> tblEmployee
</span><span class="kwd">join</span><span class="pln"> tblDepartment
on tblEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> tblDepartment</span><span class="pun">.</span><span class="typ">DeptId</span><span class="pln">
</span><span class="typ">Group</span><span class="pln"> </span><span class="typ">By</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pln">
</span>

To look at view definition -

<span class="pln">
sp_helptext vWName 
</span>

To modify a view -

<span class="pln">
ALTER VIEW statement  
</span>

To Drop a view -

<span class="pln">
DROP VIEW vWName
</span>