Complex Views in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Complex Views in SQL Server with Examples

In this article, I am going to discuss the Complex Views in SQL Server with Examples. Please read our previous article before proceeding to this article where we discussed the Advantages and Disadvantages of Views in SQL Server with examples. At the end of this article, you will understand what exactly are Complex view is and when and how to use Complex Views in SQL Server with Examples.

What is a complex view in SQL Server?

When the view is created based on multiple tables then it is known as a complex view in SQL Server. The most important point that we need to remember is, on a complex view in SQL Server, we may or may not perform the DML operations and more ever the complex view may not update the data correctly on the underlying database tables.

Example: Complex Views in SQL Server:

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

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

Business Requirement:

Our business requirement is to create a complex view in SQL Server that will return the following result set by joining the Department and Employee table.

Let’s create a complex view in SQL Server that will return the above data. Please execute the below SQL Statement to create the View which will return the above data. As you can see, the below view is created based on two tables i.e. Department and Employee, we call this a Complex View in SQL Server.

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

Now execute the following query which will return the data as expected.
SELECT * FROM vwEmployeesByDepartment

UPDATE DML Operation on Complex View in SQL Server:

Now, let’s update, Pranaya’s department, from IT to HR. As you can see in our Employee table, at the moment, there are 2 employees (Pranaya and Sambit) in the IT department. Please execute the following UPDATE statement to update the Department Name from IT to HR of the Employee whose name is Pranaya.
Update vwEmployeesByDepartment SET DepartmentName =’HR’ where Name = ‘Pranaya’
Once you execute the above UPDATE statement, then make a select query on the view as shown below
SELECT * FROM vwEmployeesByDepartment
Once you execute the above query, you will get the following output.

Notice that along with Pranaya, Sambit’s department is also changed from IT to HR. To understand the reasons for incorrect UPDATE, select the Data from Department and Employee base tables and you will get the following data.

If you notice, the UPDATE statement actually updated the Department Name from IT to HR in the Department table, instead of updating the DeptID column in the Employee table. So, the conclusion is – If a view is based on multiple tables, and if we update the view, it may not update the underlying base tables correctly. To correctly update a complex view, that is based on multiple tables INSTEAD OF Triggers are used which we will discuss in a later article.

Example: Updating two tables data using Complex View in SQL Server

Let us see what happens when we try to update two different tables record by using a complex view in SQL Server. First correct the department table, change the department name to IT where ID is 1 in the Department table by executing the below UPDATE statement.
UPDATE Department SET Name = ‘IT’ WHERE ID = 1
Let’s update the Salary and department name of Pranaya by executing the below UPDATE statement.

UPDATE 	vwEmployeesByDepartment SET 
        DepartmentName ='HR', 
        Salary = 50000
WHERE	Name = 'Pranaya'

When we execute the above update statement it gives us the error as View or function ‘vwEmployeesByDepartment’ is not updatable because the modification affects multiple base tables.

It clearly says that we cannot update a view if it affects more than one base table. In our update statement, we are trying to update the Department Name from the Department table and salary from the Employee table and hence the update statement failed and we got the above error.

Example:

Now we want to update the Salary of Pranaya from 25000 to 50000 by executing the below update statement.
UPDATE vwEmployeesByDepartment SET Salary = 50000 WHERE Name = ‘Pranaya’
When we execute the above update statement it updates the Salary as expected. To check, retrieve the data from the Employee table by executing the below SELECT statement.
SELECT * FROM Employee
Once you execute the above select statement, you will give the following results. You can see it updates the Salary correctly in the Employee table.

Conclusion:

1. In a Complex View, if your update statement affects one base table, then the update succeeded but it may or may not update the data correctly.
2. if your update statement affects more than one table, then the update failed and we will get an error message stating “View or function ‘vwEmployeesByDepartment’ is not updatable because the modification affects multiple base tables”.

In the next article, I am going to discuss how to use the Check Encryption and Schema Binding option in SQL Server views with examples. Here, in this article, I try to explain the Complex Views in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.