Post
Ask Question

Views In SQL Server

View are virtual tables that are compiled at runtime. Data associated with the views are not physically stored in view, they are present in base tables. In SQL Server we create view for security purpose as it restricts user to view columns actually present in the base tables. In SQL Server we can create a view using the following syntax:

  1. CREATE VIEW [dbo].[View_Employee]    
  2. AS    
  3. SELECT     Emp_ID, Emp_name, Emp_Sal    
  4. FROM         dbo.Employee    
  5.     
  6. GO    
SQL Server has two types of views.:
  1. System Defined Views: These views are predefined views present in master database.They are further classified into three types:

    1. Information Schema View: In SQL Server we have around 20 Information Schema View used to display information for database like tables and columns. FoR example, Information_Schema.Tables,Information_Schema.columns.

    2. Catalog View: These were introduced in SQL Server 2005 and used to show self-describing information. For example, Sys.Tables.

    3. Dynamic Management View: These were also introduced in SQL server 2005. The view give administrative information of database current state.

  2. User Defined Views: These Views are defined by users. They are of two types:

    1. Simple View: When we create a view on single table then it is called simple view. Example:
      1. CREATE VIEW [dbo].[View_Employee]    
      2. AS    
      3. SELECT     Emp_ID, Emp_name, Emp_Sal    
      4. FROM         dbo.Employee   
    2. Complex View: When we create a view on more than one table then it is called a complex view. Example:
      1. CREATE VIEW[dbo].[View_CompleteUser_Details]  
      2. AS  
      3. SELECT dbo.tblUserLogins.sno, dbo.Employee.Emp_ID, dbo.tblUserLogins.username, dbo.tblUserLogins.password, dbo.Employee.Emp_name, dbo.Employee.Emp_Sal  
      4. FROM dbo.tblUserLogins CROSS JOIN  
      5. dbo.Employee  
      6. GO  

Note:

  1. We can insert, update and delete data from simple view only when we have primary key and all not null fields are in view.
  2. We cannot insert/delete data in complex view we can only update it.