Different Types of views in sql server

Print 8 min read

31 Aug 2022

Intermediate

277K Views

Views are virtual tables that are compiled at runtime. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally, we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query the view as a table. We can make an index, trigger on the view.

In SQL Server we make views for security purposes since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make a view. One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the database table.

A view may act as a filter on the certain tables which are being referenced in the view. The query that is defined for the view can be from one or multiple different tables or from other views in the current or other databases. And the distributed queries may also be used to define the views in SQL servers that use the data from multiple data sources. It can be helpful in certain scenarios such as if you want to combine the similarly structured data from the different servers, then each of them stores the data for a different region of your organization.

The syntax for Creating a View

The view can be created using the keyword "VIEW" followed by the view name as given below.

 CREATE VIEW view_name
AS
 select_statement[]

Types of Views

In SQL Server, there are two different types of views are available to be created that are system-defined views and user-defined views and they can be used to define the views in a manner to get the all basic details such as domain usage, checking constraints, getting columns, assemblies, object and column along with the required user-defined views as well.

In SQL Server we have two types of views.

System Defined Views
System-defined Views are predefined Views that already exist in the Master database of SQL Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user-defined database.
The system defined views will expose the metadata of the database and they can be used to get the all possible information about the instance of SQL Server or the database objects, columns, objects, contains, i.e. the "sys.databases" view to returning information about the user-defined databases which are available in the SQL server instance.
We have the following types of system defined views.
1. Information Schema View
  In SQL Server we have twenty different schema views. These are used to display information in a database, like as tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.
```
 --Create a table 
create table Employee_Test
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Technology varchar(55),
 Emp_Sal decimal (10,2),
 Emp_Designation varchar(20)
)
--To view detailed information of the columns of table Employee_Test
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Employee_Test'
```
2. Catalog View
  Catalog Views were introduced with SQL Server 2005. These are used to show database self-describing information.
```
 select * from sys.tables 
```
3. Dynamic Management View
  Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information about the database about the current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance. These are of two types
  1. Server-scoped Dynamic Management View
    These are stored only in the Master database.
  2. Database-scoped Dynamic Management View
    These are stored in each database.
```
 --To see all SQL Server connections
SELECT connection_id,session_id,client_net_address,auth_scheme
FROM sys.dm_exec_connections 
```

User Defined Views

These types of views are defined by users. We have two types of user-defined views.

Simple View

When we create a view on a single table, it is called a simple view.

 --Now Insert data to table Employee_Test 
Insert into Employee_Test values ('Amit','PHP',12000,'SE');
Insert into Employee_Test values ('Mohan','ASP.NET',15000,'TL');
Insert into Employee_Test values ('Avin','C#',14000,'SE');
Insert into Employee_Test values ('Manoj','JAVA',22000,'SSE');
Insert into Employee_Test values ('Riyaz','VB',18000,'TH');
-- Now create view on single table Employee_Test
create VIEW vw_Employee_Test
AS
Select Emp_ID ,Emp_Name ,Emp_Designation
From Employee_Test

 -- Query view like as table
Select * from vw_Employee_Test

In the simple view, we can insert, update, and delete data. We can only insert data in a simple view if we have a primary key and all not null fields in the view.

 -- Insert data to view vw_Employee_Test
insert into vw_Employee_Test(Emp_Name, Emp_Designation) values ('Shailu','SSE')
-- Now see the affected view
Select * from vw_Employee_Test

 -- Update data to view vw_Employee_Test
Update vw_Employee_Test set Emp_Name = 'Pawan' where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test

 -- Delete data from view vw_Employee_Test
delete from vw_Employee_Test where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test

Complex View

When we create a view on more than one table, it is called a complex view.

 --Create another table
create table Personal_Info
(
 Emp_Name varchar(55),
 FName varchar(55),
 DOB varchar(55),
 Address varchar(55),
 Mobile int,
 State varchar(55)
)
-- Now Insert data
Insert into Personal_Info values ('G.Chaudary','22-10-1985','Ghaziabad',96548922,'UP');
Insert into Personal_Info values ('B.S.Chauhan','02-07-1986','Haridwar',96548200,'UK');
Insert into Personal_Info values ('A.Panwar','30-04-1987','Noida',97437821,'UP');
Insert into Personal_Info values ('H.C.Patak','20-07-1986','Rampur',80109747,'UP');
Insert into Personal_Info values ('M.Shekh','21-10-1985','Delhi',96547954,'Delhi');
-- Now create view on two tables Employee_Test and Personal_Info
Create VIEW vw_Employee_Personal_Info
As
Select e.Emp_ID, e.Emp_Name,e.Emp_Designation,p.DOB,p.Mobile
From Employee_Test e INNER JOIN Personal_Info p
On e.Emp_Name = p. Emp_Name

 -- Now Query view like as table
Select * from vw_Employee_Personal_Info

We can only update data in a complex view. We can't insert data in a complex view.

 --Update view 
update vw_Employee_Personal_Info set Emp_Designation = 'SSE' where Emp_ID = 3
-- See affected view
Select * from vw_Employee_Personal_Info

Note

We make views for security purposes since it restricts the user to view some columns/fields of the table(s).
One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the database table

Summary

In SQL Server, a view is similar to the virtual table whose values are defined by the user-written queries. In another word, we can say that a view is a name given to a query that can be used as a collection of multiple columns of the table that can be referenced for showing the collection of data from multiple sources. The rows and columns of a view come from tables referenced by a query whether it can be a two or the multiple tables accordingly.

The primary usage of the view in SQL Server is to prevent the security breach and It does it for the users from seeing specific columns and rows from tables randomly. The view will only show the data that is returned by the specific query that was declared when the view was created. The rest of the information is completely hidden from the end-user, unlike the normal table definition.

In this article, I try to explain the view in SQL server with an example. I hope after reading this article your SQL views concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.