Views in SQL Server with Examples
In this article, I am going to discuss the Views in SQL Server with examples. Views are one of the most important concepts in SQL Server. If you want to become a SQL Server developer then you should know how to when to use the Views in SQL Server. In this article and in a few upcoming articles, we are going to cover most of the concepts of Views in SQL Server.
What is a View in SQL Server?
The views in SQL Server are nothing more than a compiled SQL query. We can also consider the Views in SQL Server as virtual tables. As a virtual table, the SQL Server Views do not store any data physically by default. But if you want then you can change this default behavior that we will discuss in our Indexed Views in SQL Server article. So when we query a view it actually gets the data from the underlying database tables as shown in the below image.
Simply we can say that the views in SQL Server act as an interface between the Table(s) and the user.
What are the differences between a table and a view in SQL Server?
When we compared a view with a table we have the following differences.
- The table is physical whereas view is logical
- A Table is an independent object whereas view is a dependent object that is a view depends on the table or tables from which it is loading the data.
- When a new table is created from an existing table the new and old tables are independent themselves that is the changes of one table will not be reflected into the other table whereas if a view is created based on a table any changes that are performed on the table reflects into the view and any changes performed on the view reflected in the table also.
We are going to use the following Department and Employee table to understand the 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, DeptID INT ) GO -- Populate the Employee Table with test data INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1) INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2) INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2) INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3) INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1) INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2) GO
The syntax for creating a View in SQL Server: The View will be created by using the select statement as shown below.
How many types of views are there in SQL Server?
There are two types of views in SQL Server, they are
- Simple view or Updatable views
- Complex view or non-updatable views.
What is a simple view or Updatable view in SQL Server?
The view which is created based on the columns of a single table, then it is known as a simple view in SQL Server. We can perform all the DML operations on a simple view so that a simple view can also be called an updatable view or dynamic view.
-- View with all columns CREATE VIEW vwAllEmployees1 AS SELECT * FROM Employee -- View with specific columns CREATE VIEW vwAllEmployees2 AS SELECT ID, Name, Gender, DOB, DeptID FROM Employee
The above two views are examples of Simple or Updatable Views. That means we can perform all the DML operations on the above views. To select data from a view, we need to use the SELECT statement in the same way we used to select the data from a table.
SELECT * from vwAllEmployees1
DML Operations on the View:
Select Operation on View
SELECT * FROM vwAllEmployees1
When we execute the above query it will display the following results
Insert Operation on View
INSERT INTO vwAllEmployees1 (ID, Name, Gender, DOB, DeptID) VALUES(7, ‘Rohit’, ‘Male’, ‘1995-04-19 10:53:27.060’, 3)
When we execute the above insert statement, it will successfully insert the record into the Employee table. To check issue a select query against the Employee table as shown below
SELECT * FROM Employee
It will give the result
As you can see the new record is inserted into the Employee table.
Update Operation on Views in SQL Server:
Let’s try to update the details of the employee whose id is 7 using the view.
UPDATE vwAllEmployees1 SET Name = 'Rohit Kumar', DOB = '1996-02-29 10:53:27.060', DeptID = 1 WHERE ID = 7
Once you execute the above SQL Query, to check whether the data is updated successfully or not make a select query against the Employee table as shown below.
SELECT * FROM Employee
It will retrieve the following data.
As you can see the Employee whose ID is 7 is successfully updated as expected.
Delete Operation on View in SQL Server:
Let’s try to Delete the Employee whose ID is 7 using the view
DELETE FROM vwAllEmployees1 where ID = 7
Once we execute the above delete operation then check the Employee table and you will see that the Employee whose ID is 7 is deleted as expected from the Employee table.
So this proofs that we can perform the ALL the DML operations on a Simple View which is also called Updatable View in SQL Server.
What is a complex view in SQL Server?
When we create a view on more than 1 table then it is known as a complex view and on a complex view, we cannot perform DML operations so that a complex view is also called the non-updatable or static view.
CREATE VIEW vwAllEmployees3 AS SELECT emp.ID, emp.Name, emp.Gender, emp.DOB, dep.Name as DepartmentName FROM Employee emp INNER JOIN Department dep on emp.DeptID = dep.ID
NOTE: A view that is created based on a single table will also be considered as a complex view provided if the query contains any of the following.
Distinct. Aggregate Function, Group By Clause, having Clause, calculated columns and set operations.
CREATE VIEW vwAllEmployees4 AS SELECT Gender, Count(*) as TotalEmployee FROM Employee Group BY Gender
Can we drop a table that has dependent views on it?
Yes, you can drop a table even if any dependent views are associated with it, but the views that are associated with it will not be dropped. They will still execute in the database only with the status as inactive object and all those views become active and start functioning provided the table is recreated.
Can we create a view based on other views in SQL Server?
Yes, It is possible in SQL Server to create a view based on other views.
Can we update the views in SQL Server?
Yes, in SQL server views can be updated. However, updating a view that is based on multiple tables, may not update the base tables correctly. To correctly update a view that is based on multiple tables we can make use of INSTEAD OF triggers in SQL Server.
In the next article, I am going to discuss why we need Views with examples. Here, In this article, I try to explain the 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.