What is a View in sql server and its advantages ?

 

A view is nothing more than a saved query.  A view can also be considered as a virtual table.

DeptId DeptName
1 IT
2 Payroll
3 HR
4 Admin

 

Id Name Salary Gender DepartmentId
1 Jhon 5000 Male 3
2 Mike 3400 Male 2
3 Pam 6000 Female 1
4 Todd 4800 Male 4
5 Sara 3200 Female 1
6 Ben 4800 Male 3

Select Query

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Result

Id Name Salary Gender DeptName
1 Jhon 5000 Male HR
2 Mike 3400 Male Payroll
3 Pam 6000 Female IT
4 Todd 4800 Male Admin
5 Sara 3200 Female IT
6 Ben 4800 Male HR

Creating View using select Query

Create View vWEmployeesByDepartment

As

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Select * from  vWEmployeesByDepartment

Result

Id Name Salary Gender DeptName
1 Jhon 5000 Male HR
2 Mike 3400 Male Payroll
3 Pam 6000 Female IT
4 Todd 4800 Male Admin
5 Sara 3200 Female IT
6 Ben 4800 Male HR

Advantages of views

  • Views can be used to reduce the complexity of the database schema
  • Views can be used as a mechanism to implement row and column level security

Example if IT department employees only to show

Create View vWITEmployeesByDepartment

As

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Where tblDepartment.DeptName=’IT’

Select * from  vWITEmployeesByDepartment

Result

Id Name Salary Gender DeptName
3 Pam 6000 Female IT
5 Sara 3200 Female IT

Example if salary to hide

Create View vWNonconfedentialdata

As

Select Id, Name,  Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Select * from vWNonconfedentialdata

Result

Id Name Gender DeptName
1 Jhon Male HR
2 Mike Male Payroll
3 Pam Female IT
4 Todd Male Admin
5 Sara Female IT
6 Ben Male HR
  • Views can be used to present aggregated data and hide detailed data.

Example

Create view VwsummarizedData

As

select DeptName, Count(ID) as Total

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Group by DeptName

 

Select * from VwsummarizedData

Result

DeptName Total
Admin 1
HR 2
IT 2
Payroll 1

 

To modify view – ALTER VIEW statement

To droop a view – DROP VIEW viewname

Leave a Reply