Database : Different Types of SQL Server Views

Views are virtual tables that are compiled at run time. 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 view like as table. We can make index, trigger on view.

In Sql Server we make views for security purpose 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 view.One more advantage of Views is, data abstraction since the end user is not aware of all the data present in database table.

Syntax for View

 CREATE VIEW view_name
AS
 select_statement[] 

Types of Views

In Sql Server we have two types of views.

1. 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.
   
   We have 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 of 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 of 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 
      
      

      

2. User Defined Views

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

   1. Simple View

      When we create a view on a single table, it is called 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 simple view we can insert, update, delete data. We can only insert data in simple view if we have 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 
      
      

      

   2. Complex View

      When we create a view on more than one table, it is called 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 complex view. We can't insert data in 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

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