Why we need views in SQL Server

Why we need views in SQL Server with examples

In this article, I am going to discuss why we need views in SQL Server with some examples. Please read our previous article before proceeding to this article where we discussed Views in SQL Server with examples. At the end of this article, you will be in a better position to understand why and when we need to use views in SQL Server.

Let us understand why we need views in SQL Server.

We are going to use the following Department and Employee table to understand why we need views in SQL Server.

why we need views in SQL Server

Please use below SQL Script to create and populate the Department and Employee table with some test data.

-- Create Department Table
CREATE TABLE Department
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50)
)
GO

-- Populate the Department Table with test data
INSERT INTO Department VALUES(1, 'IT')
INSERT INTO Department VALUES(2, 'HR')
INSERT INTO Department VALUES(3, 'Sales')

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  Salary DECIMAL(18,2),
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 25000, 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 30000, 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060',40000, 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 35000, 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 27000, 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 33000, 2)
GO
Let’s discuss the why we need views in SQL Server.
Hiding the complexity

The Views can be used as a mechanism to reduce the complexity of the database schema for non-IT users. Let’s create a view which will retrieve the data from both the Department and Employee table as shown below.

CREATE VIEW vwEmployeesByDepartment
AS
SELECT	emp.ID, 
        emp.Name, 
        emp.Salary, 
        emp.Gender, 
  dept.Name AS DepartmentName
FROM Employee emp
INNER JOIN Department dept
ON emp.DeptID = dept.ID

The above vwEmployeesByDepartment view hides the complexity of joins. The Non-IT users find it easy to query the above view, rather than writing complex joins.

The Views in SQL server can also be used as a mechanism to implement the row and column level security.

Row Level Security:

For example, if you want an end user to have access only to the IT Department employees. If you grant him access to the underlying Employee and Department tables, then he will be able to see the information of all the department employees. To restrict this, you can create a view, which will return only the IT Department employees, and grant the user access to the view rather than the underlying database tables.

Let’s create a view that only returns the IT department employees:

CREATE VIEW vwITDepartmentEmployees
AS
SELECT	emp.ID, 
        emp.Name, 
        emp.Salary, 
        emp.Gender, 
  dept.Name AS DepartmentName
FROM Employee emp
INNER JOIN Department dept
ON emp.DeptID = dept.ID
WHERE dept.Name = 'IT'

Now if we execute the above view then it only displays the IT Department employees as shown below.

SELECT * FROM vwITDepartmentEmployees

OUTPUT:

Why we need views in SQL Server

Column Level Security:

As you know the Salary is the confidential information for any organization and I want to prevent access to that column. If we provide access to the Employee and Department table, then the user can view the salary of any employees. To restrict this, we can create a view, which excludes the Salary column, and then grant the end user access to the view rather than the database tables.

Let’s create a view that will return all columns expect the Salary column.

CREATE VIEW vwEmployeesByDept
AS
SELECT	emp.ID, 
        emp.Name, 
        emp.Gender, 
        DOB,
  dept.Name AS DepartmentName
FROM Employee emp
INNER JOIN Department dept
ON emp.DeptID = dept.ID

Now when we execute the above view, it will display all the columns data except the salary column as shown below.

SELECT * FROM vwEmployeesByDept

Result:

Why we need views in SQL Server

The Views in SQL Server can also be used to present only the aggregated data and hide the detailed data.

Let’s create a view that will return the total number of employees by department.

CREATE VIEW vwEmployeesCountByDepartment
AS
SELECT	dept.Name AS DepartmentName,
  COUNT(*) AS TotalEmployees
FROM Employee emp
INNER JOIN Department dept
ON emp.DeptID = dept.ID
GROUP By dept.Name

When we execute the above view, it will display only the aggregated data as shown below.

SELECT * FROM vwEmployeesCountByDepartment

Result:

Why we need views in SQL Server

So, in short, We Need Views in SQL Server
  1. To protect the data. If we have a table containing sensitive data in certain columns, we might wish to hide those columns from certain groups of users. For instance, customer names, addresses, and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the original tables.
  2. A view is a logical table but what it stores internally is a select statement that is used for creating the view. So that whenever a user performs an operation on the view like select, insert, update or delete internally the view performs those operations on a table.
  3. Simply we can say that view will act as an interface between the data provider (Table) and the User.
  4. The view is created based on a table any changes that are performed on the table reflects into the view any changes performed on the view reflect on the table also.

In the next article, I am going to discuss the Complex Views in SQL Server with some example.

SUMMARY

In this article, I try to explain Why we need views in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *