Advantages and Disadvantages of Views in SQL Server

Advantages and Disadvantages of Views in SQL Server

In this article, I am going to discuss Advantages and Disadvantages of Views in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the basics of 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 as well as the limitations of Views.

  1. Advantages of views in SQL Server
  2. Hiding the complexity of a Complex SQL Query
  3. Implementing Row Level Security
  4. Implementing Column Level Security
  5. Presenting the Aggregated data by Hiding Detailed data
  6. Limitations and Dis-Advantages of Views in SQL Server
  7. We cannot pass parameters to SQL Server views
  8. Cannot use Order By clause with views without specifying FOR XML, OFFSET or TOP
  9. The Views cannot be created based on Temporary Tables in SQL Server
  10. We cannot associate Rules and Defaults with SQL Server views
Let us understand why we need views in SQL Server.

We are going to use the following Department and Employee table to understand the advantages of using views in SQL Server.

Advantages and Disadvantages of 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
Advantages of views in SQL Server

We are getting the following advantages of using Views in SQL Server.

  1. Hiding the complexity
  2. Implementing Row and Column Level Security.
  3. Presenting the aggregated data by hiding the detail data.
Hiding the complexity of a Complex SQL Query

In SQL Server, we can use the Views 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 SQL joins. Now, the Non-IT users find this view easy to query rather than writing the complex joins.

Implementing Row Level Security:

For example, you want an end-user to access only to the IT Department employees. If you grant 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

Advantages Views in SQL Server

Implementing 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 except 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

Disadvantages of Views in SQL Server

Presenting the Aggregated data by Hiding Detailed data:

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

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 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.
Limitations and Dis-Advantages of Views in SQL Server:

Let us discuss the limitations of views in SQL Server. We are going to use the following Employee table to understand the limitations of views in SQL Server.

Limitations of Views in SQL Server

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

-- 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 Limitations of Views in SQL Server:

In SQL Server, it is not possible to pass parameters to a view. The Inline Table Valued Functions in SQL Server are an excellent replacement for the parameterized views.

-- Error: Cannot pass Parameters to a View
CREATE VIEW vwEmployeeDetailsByGender
@Gender varchar(20)
AS
SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
WHERE Gender = @Gender
The Table-Valued functions in SQL Server can be used as a replacement for parameterized views.
CREATE FUNCTION fnEmployeeDetailsByGender
(
  @Gender VARCHAR(20)
)
RETURNS Table
AS
RETURN  
(SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM Employee 
WHERE Gender = @Gender)

-- Calling the function
SELECT * FROM dbo.fnEmployeeDetailsByGender('Male')

It will give us the below output

Limitations of Views in SQL Server

Rules and Defaults cannot be associated with the views in SQL Server. This is because the views do not store the data physically they are actually Saved SQL statements. Therefore, it makes sense that they do not have any support for Rules and Defaults.

The ORDER BY clause is invalid in views unless TOP, OFFSET or FOR XML is also specified.

CREATE VIEW vwEmployeeDetailsSortedByName
AS
SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
ORDER BY Name

If we use ORDER BY, we will get an error stating – The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET or FOR XML is also specified.

Let’s see how to use TOP Clause to support Order By clause in SQL Server.
CREATE VIEW vwEmployeeDetailsSortedByName
AS
SELECT TOP 100 PERCENT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
ORDER BY Name

To learn more about TOP along with PERCENT please find the below article.

https://dotnettutorials.net/lesson/top-n-clause-sql-server/

Views cannot be created based on temporary tables.

Let’ s see an example to understand this.

Create Table ##TestTempTable(Id int, Name nvarchar(20), Gender nvarchar(10))

Insert into ##TestTempTable values(101, ABC, 'Male')
Insert into ##TestTempTable values(102, PQR, 'Female')
Insert into ##TestTempTable values(103, XYZ, 'Female')

-- Error: Cannot create a view on Temp Tables
Create View vwOnTempTable
as
Select Id, Name, Gender
from ##TestTempTable
So, in short, the following are the limitations and Dis-Advantages of Views in SQL Server
  1. We cannot pass parameters to SQL Server views
  2. Cannot use an Order By clause with views without specifying FOR XML, OFFSET or TOP
  3. The Views cannot be created based on Temporary Tables in SQL Server
  4. We cannot associate Rules and Defaults with SQL Server views

In the next article, I am going to discuss the Complex Views in SQL Server with some examples. Here, in this article, I try to explain Advantages and Disadvantages of Views in SQL Server step by step with some examples. I hope this Advantages and Disadvantages of Views in SQL Server article. 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 *