View In SQL | Types Of Views In SQL Server [2021]

In this article, we will discuss the different types of views in SQL Server with examples. We will also try to create the view, modify the view, update the view and drop the view in SQL Server.

What is a view in SQL?

A view in SQL server is nothing more than a kind of virtual table based on the result set of SQL statements from one or more tables.

In SQL, View can be used to provide a security mechanism by allowing users to access data through the view, without giving the user permission to directly access the underlying base or actual tables referenced in the view.

• • • In SQL, View is a virtual table for the base tables, It doesn’t have physical existence.
    • A View stores only the Select Query Statements instead of the actual data of the underline base tables.
    • A View in SQL can be created by selecting columns from one or more tables in the database.
    • A View can be created with the help of SQL “Select Statement”.
    • The view is a logical entity, whereas the table in SQL is a physical entity.
    • A View can be used to provide a backward compatible interface to emulate a table whose schema has been changed.

In SQL, Views can be used to structure data from single or multiple tables. It helps to simplify the complex SQL queries, restricts users from accessing the actual base table data, and summarizes data from various tables to create reports.

View in SQL Server can be used to provide row and column level security. We can allow a user to access only selected columns of a table instead of accessing the whole table records.

Syntax to create view in SQL server

The following is the syntax to create a view in the SQL server.

What are the types of views in SQL?

In SQL Server, there are two main types of views, namely System Defined Views and User Defined Views are available.

• • System Defined Views
    1. 1. Information Schema View
       2. Catalog View
       3. Dynamic Management View
  • User Defined Views
    1. 1. Simple View
       2. Complex View

Let’s first discuss the User-defined view.

User Defined Views in SQL

These types of views in SQL are generally defined by the users. There are two types of User-defined views, namely Simple View and Complex View.

Simple View In SQL

In SQL, A View created on a single table is known as a simple view.
The simple view support DML operation, we can insert, update, or delete records if the base table contains the primary key and not null columns.

How to create a simple view in SQL?

Let’s first create a table with few records and then create a simple view on this table.

Following is the example to create a simple view in SQL server.

-- Creating a new table "tblEmployee".
CREATE TABLE tblEmployee
(
  [Id] INT PRIMARY KEY,
  [Name] NVARCHAR(30),
  [Gender] VARCHAR(10),
  [City] VARCHAR(50),
  [DeptId] INT
);

--Inserting data into the "tblEmployee" table.

INSERT INTO tblEmployee VALUES (101,'Shekh Ali','Male','Delhi', 1),
                               (102,'Mark Adam','Male','New York', 1),
							   (103,'Julie Folden','Female','Chicago', 2);

-- SQL Create a Simple View on base table "tblEmployee"

CREATE VIEW [SimpleViewOnTblEmployee]
AS
SELECT Id, Name, Gender, City, DeptId
FROM tblEmployee;

Let’s query view to show the records like a table.

-- Show records from a simple view like a table.

SELECT * FROM [SimpleViewOnTblEmployee];

Insert operation on Simple view in SQL

In Simple view, we are allowed to insert, update, or delete records. Following is the query to insert a new record in the simple view.

-- Insert record into Simple view

INSERT INTO [SimpleViewOnTblEmployee] VALUES (104,'Salim Khan','Male','Delhi', 1);

-- Let's see the affected view

SELECT * FROM [SimpleViewOnTblEmployee];

Update operation on simple view in SQL

When we execute the following update query, It will successfully update the record from the view. Let’s update the record whose Id is 104.

-- Update record into Simple view

UPDATE [SimpleViewOnTblEmployee] SET Name='Daniel' WHERE Id = 104;

To check the updated record, select query against the simple view “SimpleViewOnTblEmployee” as shown below.

-- Select record from the sample view
SELECT * FROM [SimpleViewOnTblEmployee] ;

Delete operation on simple view in SQL

Let’s execute the following SQL command to delete the record whose Id is 102 from the simple view.

-- Delete data from view "SimpleViewOnTblEmployee"
Delete from [SimpleViewOnTblEmployee] where Id = 102 ;

-- Let's see the affected view after delete operation
Select * from [SimpleViewOnTblEmployee] ;

In the above image, we can see that the record whose Id was 102 is deleted successfully.

Complex View In SQL

A View created on more than one base table is known as a  Complex View  . Complex view doesn’t allow to perform DML operation. It is also known as a non-updatable or static view.

The complex view can contain a group by clause, order by clause, Aggregate Functions, or join conditions.

-- Create a new [DEPARTMENT] table.

CREATE TABLE DEPARTMENT
(
	[Id] [int] PRIMARY KEY NOT NULL,
	[DeptName] [nvarchar](50) NULL,
);

-- INSERT RECORD INTO THE DEPARTMENT TABLE.

INSERT INTO DEPARTMENT VALUES(1,'IT'),(2,'HR'),(3,'FINANCE');

-- Show record from the department table.
SELECT * FROM DEPARTMENT;

-- Now create view on two tables [tblEmployee] and [Department]
CREATE VIEW vw_tblEmployee_Department
AS
SELECT e.Id, e.Name,e.Gender,e.City,d.DeptName
FROM tblEmployee e INNER JOIN Department d
ON e.DeptId = d.Id;

Now run the below query to show the records from the [vw_tblEmployee_Department] complex view.

-- Run the below query to show the records from the complex view [vw_tblEmployee_Department] .
SELECT * FROM vw_tblEmployee_Department;

Update Complex View in SQL

Note: We are allowed to update the existing records in a complex view, but can’t insert the new records.

If a complex view is based on multiple tables, might be it will not allow updating the base table correctly. To update the complex view, instead of trigger can be used.

Following is the SQL query to update the complex view.

-- Query to update [City] in the in the complex view [vw_tblEmployee_Department].
UPDATE vw_tblEmployee_Department set City ='Mumbai' Where Id = 101;

Once we run the above query, we can see that the City is updated from “Delhi” to “Mumbai” for Id 101.

System Defined Views In SQL

In SQL Server, the  System defined views  are predefined Views that already exist in the master database.

The Master database generally acts as a template database for all other user-defined databases in SQL server. It contains nearly 230 predefined views.
The following are the three systems defined views, namely Information Schema View, Catalog View, and Dynamic Management View.

Information Schema View In SQL ServerS nII LSn n

In SQL Server, there are approx 21 different Information schema views are available. These types of views start with  INFORMATION_SCHEMA  as prefix and used to provide information about the database objects such as tables, columns, domains, check constraints etc.
Following is the SQL statement to view detailed information of the columns of table tblEmployee.

-- To view detailed information of the columns of table tblEmployee

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tblEmployee';

-- OR

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tblEmployee';

The following INFORMATION_SCHEMA.CHECK_CONSTRAINTS is used to return the information about the constraints of a table.

-- Return information about the constraints on a table

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
WHERE TABLE_NAME ='tblEmployee'; 

The following INFORMATION_SCHEMA.VIEWS will return all the views present in the current database.

--This INFORMATION_SCHEMA.VIEWS will return all the views present in the current database. 
SELECT * FROM INFORMATION_SCHEMA.VIEWS ;

What are Catalog Views in SQL Server?

As per MSDN: Catalog views are used to return information used by the SQL server database engine. Catalog views are mainly used to provide information about a database metadata and its objects such as tables and views.
The  sys.objects  catalog view is referred as the base view, and the  sys.tables  view is called the derived view. The sys.tables catalog view in SQL server returns the columns that are specific to tables and also all the columns that the sys.objects catalog view returns.

The sys.objects catalog view returns rows of objects other than tables, such as stored procedures and views. Once a new table is created, the metadata for the table is returned in both sys.objects and sys.tables views.

Use of catalog views:

• • Catalog views provide information about the metadata of databases.
  • Catalog views returns rows of objects other than tables, such as stored procedures and views.
  • Catalog views provide descriptive column names, which might be very helpful for users to understand the data.

-- Catalog view example for tables:
SELECT * FROM sys.tables;

-- Catalog view example for database
SELECT name, database_id, compatibility_level,*
FROM sys.databases
WHERE name LIKE 'master%';

What Is Dynamic Management View In SQL?

The  Dynamic Management Views  were introduced in SQL Server 2005. These Views provide the administrator information about the server state to diagnose problems, monitor the health and the current-state of the SQL server machine.
These views help the administrator to analyze database-related problems and tune the SQL server for optimal performance.

These are two types of Dynamic Management View.

Server Scoped Dynamic Management View: These types of views are stored only in the Master database.

Database Scoped Dynamic Management View: These types of views are stored in each database.

--This returns current session information 
SELECT login_name ,COUNT(session_id) AS session_count  
FROM sys.dm_exec_sessions  
GROUP BY login_name;

--To see all SQL Server connections
SELECT connection_id,session_id,client_net_address,auth_scheme
FROM sys.dm_exec_connections ;

FAQs

Conclusion

In this article, we discussed different types of views in SQL, such as System Defined Views and User Defined Views.

I hope you like this post. If you have any questions, please post your question or suggestion in the comment section.

Thanks for visiting 🙂

Shekh Ali

I am a Sr. Software engineer. I started this blog as a way to contribute back to the developer community for everything I have learned along the way from others.

Summary

Article Name

View In SQL | Types Of Views In SQL Server [2021]

Description

A view in SQL Server is nothing more than a kind of virtual table based on the result set of SQL statements from one or more tables. View in SQL Server can be used to provide row and column level security.