Advantages and Disadvantages of Views in SQL Server

Back to: SQL Server Tutorial

Advantages and Disadvantages of Views in SQL Server

In this article, I am going to discuss the 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 the Advantages and Disadvantages of 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 the SQL Script to create and populate the Department and Employee table with required sample 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 detailed data.
Hiding the Complexity of a Complex SQL Query using Views in SQL Server

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 that 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 Server Joins. Now, the Non-IT users find this view easy to query rather than writing the complex joins.

Implementing Row Level Security using Views in SQL Server:

Let us see how to implement Row Level Security using Views in SQL Server. For example, you want an end-user to access only the IT Department employees. If you grant access to the underlying Employee and Department tables, then the end-user will be able to see the information of all the department employees. To restrict this, what you can do is, you will create a view, which will return only the IT Department employees, and grant the end-user to access the view rather than the underlying database tables. Let’s create a view that only returns the IT department employees by joining Employee and Department table by executing the below query.

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 execute the vwITDepartmentEmployees as shown below.
SELECT * FROM vwITDepartmentEmployees
Once you execute the above SELECT statement, it will only display the IT Department employees as shown in the below image.

Implementing Row Level Security using Views in SQL Server

Implementing Column Level Security using Views in SQL Server:

Let us understand how to implement Column Level Security using Views in SQL Server. As you know the Salary is 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 employee. To restrict this, what we can do is, we will 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 by executing the below SQL statements.

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 execute the vwITDepartmentEmployees as shown below.
SELECT * FROM vwEmployeesByDept
Once you execute the above SELECT statement, it will only display all the columns data except the salary column as shown in the below image.

Implementing Column Level Security using Views in SQL Server

Presenting the Aggregated data by Hiding Detailed data using 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

Now execute the vwEmployeesCountByDepartment as shown below.
SELECT * FROM vwEmployeesCountByDepartment
Once you execute the above SELECT statement, it will display only the aggregated data as shown in the below image.

Presenting the Aggregated data by Hiding Detailed data using 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 names and addresses. 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 reflect 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 and Disadvantages of views in SQL Server. We are going to use the following Employee table to understand the limitations and Disadvantages of views in SQL Server.

Limitations and Dis-Advantages of Views in SQL Server

 

Please use the below SQL Script to create and populate the Employee table with sample 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
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)

Now you can call the function from a select class as shown below.
SELECT * FROM dbo.fnEmployeeDetailsByGender(‘Male’)
Once you execute the above function you will get the following output.

Advantages and Disadvantages of Views in SQL Server with examples

 

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 the ORDER BY clause, 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

 

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. We 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 Examples. Here, in this article, I try to explain the Advantages and Disadvantages of Views in SQL Server with Examples. I hope this Advantages and Disadvantages of Views in the SQL Server article. I would like to have your feedback. Please post your feedback, question, or comments about this Advantages and Disadvantages of Views in the SQL Server article.