Creating and Usage of View in SQL

Introduction

What is a View?

View can be described as virtual table which derived its data from one or more than one table columns. It is stored in the database. View can be created using tables of same database or different database. It is used to implement the security mechanism in the SQL Server.

For example:

Create table Emp_Details(EmpId int, EmpName nvarchar(200),
    EmpLogin nvarchar(20), EmpPassword nvarchar(20) , EmploymentDate datetime ) 

And for example, table has the following data of employees:

EmpId	EmpName	EmpLogin	Emppassword	EmploymentDate
1	EmployeeA	EmpA	EmpAPwd	        29/01/2006
2	EmployeeB	EmpB	EmpBPwd	        06/02/2007
3	EmployeeC	EmpC	EmpCPwd	        14/05/2007
4	EmployeeD	EmpD	Empd	        30/03/2008
5	EmployeeE	EmpE	EmpEPwd	        30/06/2007
6         EmployeeF  EmpF      EmpFPwd           12/09/2012 

Now suppose that the Administrator do not want that the users to access the whole data of Emp_Details table which contains some critical information (Emplogin, EmpPassword, etc.) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and gives permission for the view to the user. In this way, the administrator does not need to give access permission for the table to the user.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

The syntax for creating a View is given below:

Create View Viewname As
 Select Column1, Column2  From Tablename
 Where (Condition) Group by (Grouping Condition) having (having Condition)

For example:

Create View View_Employeeinfo As s
       Select EmpId, EmpName, employmentdate  From EmployeeInfo

Now user can use the view View_EmployeeInfo as a table to get the empid, empname and employmentdate information of the employees by using the following query:

Select  *   from  View_EmployeeInfo  where empid=3 

It would give the following result:

EmpId	EmpName	EmploymentDate
3	EmployeeC	14/05/2007

We can also use Sql Joins in the Select statement in deriving the data for the view.

Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))

and it contains the following data:

EmpId	Projectname
1	OnlineBookA
2	OnlineBookB
3	OnlineBookC
4	OnlineBookD
5	OnlineBookE

Now we can create a view Vw_EmployeeProj which gives information about the Employees and their projects:

Create view Vw_EmployeeProj As
 Select Emp_Details.EmpId, Emp_Details.EmpName,
     EmpProjInfo.Projectname from EmployeeInfo inner join
     EmpProjInfo on Emp_Details.EmpId=EmpProjInfo.EmpId

Altering an View

If we want to alter the view, then we can use the Alter View command to alter the view. For example,

Alter view Vw_EmployeeProj As
  Select Emp_Details.EmpId, Emp_Details.EmpName, 
   EmpProjInfo.Projectname from Emp_Details inner join 
   EmpProjInfo on Emp_Details.EmpId=EmpProjInfo.EmpId where Emp_Details.EmpId in (2,3,4)

Getting Information about the Views

We can use the System Procedure Sp_Helptext to get the definition about the views. For example, we can use the sp_helptext command to get the information about the view Vw_EmployeeProj.

sp_helptext Vw_EmployeeProj

Renaming the View

We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:

SP_Rename 'Old Name', 'New name'

For example, if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:

sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping a View

We can use the Drop command to drop a view. For example, to drop the view Vw_EmployeeInfo, we can use the following statement:

Drop view Vw_EmployeeInfo