articles

Home / DeveloperSection / Articles / SQL Server Views and Its Types

SQL Server Views and Its Types

Niraj Kumar Mishra7201 17-Aug-2017

 Views in a SQL are virtual tables that are compiled at run time. The data that we look in views are not physically stored  in the views , but it is stored on main Table. We can make a view on one or more database tables. Normally we take those columns in view that we need to query more time. Once you have created the view, you can query view like as table. 

  In SQL Server we make views for security reason since it restricts the user to view some columns/ of the table. Views   are  show only those columns that are present in the query which is used to making time of View .View also provide      Abstraction advantage, by which End user not see all table column that are store in database Table.  

 Syntax for View

 

         CREATE VIEW  [view_name]
          AS
           [select_statement]

 

 Types Of Views 
  In SQL we have two Types of Views..    
  1. System Defined Views and
  2. User Defined Views. 
 System Defined Views 
 System defined Views are that types of Views that already exist in the Master
database of   SQL Server. That Views (System Defined) will be automatically
joined to any user defined database. 
 User Defined Views


 When User make any view by self then that type of View are called User Defined


Views .


There are two types  of user defined views. 
  1.      Simple View
  2.      Complex View 
   Simple View


 When any user made a View by using Single table columns then that Views are called Simple view.   In a simple view we can delete, update and Insert data. Insert operation are perform on simple view   only if we have primary key and all not null fields in the view. 

Example:

SQL Server Views and Its Types


It produced following result


SQL Server Views and Its Types


Now, I make a View..

SQL Server Views and Its Types

It produced following result


SQL Server Views and Its Types


In this View we can also Insert, Update and Delete data and that changes are applied on base table.
SQL Server Views and Its Types

SQL Server Views and Its Types

And again if you want to show User table data then it show updated record, Like this

SQL Server Views and Its Types

Complex View

   When any user made a View by using more than one tables  then that Views are called Complex view.

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

SQL Server Views and Its Types

SQL Server Views and Its Types

SQL Server Views and Its Types

SQL Server Views and Its Types


We can Update data in Complex View, But we cannot Insert data in Complex view

SQL Server Views and Its Types

 Now I insert data on vw_User_Personal_Info  View then it give the following result..

SQL Server Views and Its Types

  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.



Leave Comment

Comments

Liked By