SQL Server View and view types

To understand the concept of a View, we'll directly go to an example

First I create two table called 'Employee' and 'Departments' and add data to those tables

Employee Table

Departments Table

• Assume that you need to store Employee with Department. In this point you can create a view. View is like a virtual table. A View doesn't store data. it is basically a table structure.
• We can restrict viewing unwanted data to the users using views

we can make a view as follows

create view viewEmployee_Dept
as

select emp.EmployeeName, dep.DepartmentName
from Employee as emp inner join Departments as dep on emp.DepartmentId = dep.deptid

viewEmployee_Dept













There are few types of Views, The operations which can do with views depend on these View types

1. System defined views - will be available in all user defined database

• INFORMATION_SCHEMA_TABLES
• sys.all_columns

     2.Information Schema views - display information of database, table, columns

• INFORMATION_SCHEMA.COLUMNS

     3.Catelog view - can get db special information

• sys.columns

     4. User defined views

• simple view - get data from a single table only

            ex: create view viewEmployee

                  as

                  select EmployeeName,Salary

                  from Employee

• complex view - getting data from multiple tables

           ex: create view viewEmployee_Dept

                 as

                 select emp.EmployeeName, dep.DepartmentName

                 from Employee as emp

         inner join Departments as dep on emp.DepartmentId = dep.deptid