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.

In our Simple View in SQL Server article, we already discussed that a view is nothing more than a saved SQL query. The view can also be considered as a virtual table. That means we can think the view either as a compiled SQL query or a virtual table. As the view represents a virtual table it does not physically store any data by default. When we query a view we actually retrieve the data from the underlying database tables. We also discussed that we can perform ALL the DML operations on a simple view. 

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

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 which 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

And it will give the following result

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 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 below error

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

It 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.

SUMMARY

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 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 *