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.

DeptIdDeptName
1IT
2Payroll
3HR
4Admin

 

IdNameSalaryGenderDepartmentId
1Jhon5000Male3
2Mike3400Male2
3Pam6000Female1
4Todd4800Male4
5Sara3200Female1
6Ben4800Male3

Select Query

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Result

IdNameSalaryGenderDeptName
1Jhon5000MaleHR
2Mike3400MalePayroll
3Pam6000FemaleIT
4Todd4800MaleAdmin
5Sara3200FemaleIT
6Ben4800MaleHR

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

IdNameSalaryGenderDeptName
1Jhon5000MaleHR
2Mike3400MalePayroll
3Pam6000FemaleIT
4Todd4800MaleAdmin
5Sara3200FemaleIT
6Ben4800MaleHR

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

IdNameSalaryGenderDeptName
3Pam6000FemaleIT
5Sara3200FemaleIT

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

IdNameGenderDeptName
1JhonMaleHR
2MikeMalePayroll
3PamFemaleIT
4ToddMaleAdmin
5SaraFemaleIT
6BenMaleHR
  • 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

DeptNameTotal
Admin1
HR2
IT2
Payroll1

 

To modify view – ALTER VIEW statement

To droop a view – DROP VIEW viewname

Leave a Reply

%d bloggers like this: