Complex Views in SQL Server

Complex Views in SQL Server

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 why we need Views in SQL Server with examples. At the end of this article, you will understand what exactly are Complex view 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.

Understanding the Complex View in SQL Server with Examples:

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

Complex View in SQL Server with Examples

Please use the 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

Our requirement is to create a view that will return the following results by joining the Department and Employee table.

Complex Views in SQL Server

Let’s create a view that will return the above data. As the below view created on two tables i.e. Department and Employee table, we call this view as 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
Retrieve the Data from the view vwEmployeeDetailsByDepartment

SELECT * FROM vwEmployeesByDepartment

Complex Views in SQL Server

Now, let’s update, Pranaya’s department, from IT to HR. As you can see from the above image, at the moment, there are 2 employees (Pranaya and Sambit) in the IT department.

Update vwEmployeesByDepartment SET DepartmentName =’HR’ where Name = ‘Pranaya’

Once you update the Department Name of Pranata from IT to HR, then make a select query on the view as shown below

SELECT * FROM vwEmployeesByDepartment

Complex Views in SQL Server

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. 

Complex Views in SQL Server

Actually, the UPDATE statement, 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 – 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 view, that is based on multiple tables INSTEAD OF triggers are used which we will discuss in a later article.

Let’s see another example.

First correct the department table, change the department name to IT where ID is 1 in the Department table as shown below.

UPDATE Department SET Name = ‘IT’ WHERE ID = 1

Let’s update the Salary and department name of Pranaya.

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.

Let’s see another example.

Now we want to update the Salary of Pranaya from 25000 to 50000

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.

SELECT * FROM Employee will give the below results.

Complex Views in SQL Server

As 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 statingView 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 step by step with some 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.

1 thought on “Complex Views in SQL Server”

  1. This Article is very useful for understanding whether we can perform the Update operation on the Complex view. i got to know that from this article is that we can perform the DML operations on BASE TABLE only whether it is Simple view or Complex view.

Leave a Reply

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