Please enable Javascript to correctly display the contents on Dot Net Tricks!

Different Types of SQL Server Views

  Author : Shailendra Chauhan
Updated On : 23 Sep 2016
Total Views : 164,429   
Support : SQL Server 2005,2008,2012
 

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

Summary

In this article I try to explain the view in sql server with 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.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri 6:30 AM-7:30 AM IST

Know More
3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (09:30 AM-11:00 AM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+