Views
View can be described as a virtual table which derived its data from one or more than one table columns. It is stored in the database. It is used to implements the security mechanism in the SQL Server. For example, suppose there is a table called Employeeinfo
whose structure is given below:
Create table EmployeeInfo(EmpId int, EmpName nvarchar(200),
EmpLogin nvarchar(20), Emppassword nvarchar(20) , EmploymentDate datetime )
And it contains the following data:
EmpId | EmpName | EmpLogin | Emppassword | EmploymentDate |
1 | Vivek Johari | Vivek | VikJoh | 29/01/2006 |
2 | Virender Singh | Virender | Virender | 06/02/2007 |
3 | Raman Thakur | Raman | Raman | 14/05/2007 |
4 | Uma Dutt Sharma | Uma | Uma | 30/03/2008 |
5 | Ravi Kumar Thakur | Ravi | Ravi | 30/06/2007 |
Now suppose the Administrator does not want that the users have excess to the table EmployeeInfo
which contains the 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 give permission for the view to the user. In this way, the administrator does not need to bother about giving the access permission for the table to the user.
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
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 giving query:
Select * from View_EmployeeInfo where empid=2
It would give the following result:
EmpId | EmpName | EmploymentDate |
2 | Virender Singh | 06/02/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 | Abcbank |
2 | AtoZfinancialsol |
3 | learningsystem |
4 | ebooksystem |
5 | AtoZfinancialsol |
Now we can create a view Vw_EmployeeProj
which gives information about the Employees
and its projects.
Create view Vw_EmployeeProj As
Select EmployeeInfo.EmpId, EmployeeInfo.EmpName,
EmpProjInfo.Projectname from EmployeeInfo inner join
EmpProjInfo on EmployeeInfo.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 EmployeeInfo.EmpId, EmployeeInfo.EmpName,
EmpProjInfo.Projectname from EmployeeInfo inner join
EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId where EmployeeInfo.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
Summary
This article tells us that view can be described as a virtual table which can derive its data from one or more than one table. We can create a view with the help of the Create View
command, can alter its definition with the help of Alter view
command, get its definition with the help of Sp_helptext
command, rename a view with the Sp_rename
command and drop a view with the Drop view
command.