Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Creating and Usage of View in SQL

4.23/5 (7 votes)
19 Aug 2013CPOL2 min read 131.6K  
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.

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:

SQL
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:

SQL
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:

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

For example:

SQL
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:

SQL
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.

SQL
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:

SQL
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,

SQL
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.

SQL
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:

SQL
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:

SQL
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:

SQL
Drop view Vw_EmployeeInfo

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)