Creating and usage of View in SQL
In this article we are showing views in SQL Server 2008. A view is virtual, the data from a view is not stored physically. It is a set of queries that, when applied to one or more tables, is stored in the database as an object. A view encapsulates the name of the table. A virtual table contains column and data from multiple tables.
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 implements 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 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 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 give the permission for the view to the user. In this way the administrator do not need to give the 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
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 gives 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 the 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
Hi
Thanks for approving my first article..
Bandhavi.M