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


Author: Bandhavi02 Apr 2013 Member Level: Silver   Points : 0


Thanks for approving my first article..


Author: srirama03 Apr 2013 Member Level: Gold   Points : 5

Hi All,

I appreciate that your willingness to write some sought of nice article or resource to bring in front of 'viewers'.But at the same time i am sorry to say that that your article should n't be like textbook text(all known things). when you are writing an article you have to remember that you are bringing some thing innovative or rediscover or highlighting a point some thing which most of the majority people did n't know about that.In other words your article is more in practicality as a reader i want to see that and i need that .Its my advice on my side please take it as a sportive one.I hope that your next articles meets the required criteria.

Thanks and Regards

Author: Md. Mizanur Rahman28 May 2013 Member Level: Silver   Points : 0

This Article is helpful for us who are working with Microsoft SQL Server

Author: PriK10 Jun 2013 Member Level: Bronze   Points : 0

nice article.... Keep it up...

Author: Ranajoy28 Jul 2013 Member Level: Silver   Points : 4


The definition of View is mentioned below:-

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.

Author: Phagu Mahato19 Oct 2013 Member Level: Gold   Points : 10

What is the SQL produce view Statement?

A SQL view could be a virtual table and therefore the SQL CRERATE view statement is that the SQL command that adds a brand new view to a SQL information.

A view are often accessed exploitation the SQL choose statement sort of a table. A view is made by choosing information from one or a lot of tables.

Some views also can support the SQL INSERT, SQL UPDATE and SQL DELETE statements. therein case, the view should check with one table and embody all NOT NULL columns of that table.
Why Use the SQL produce view Statement?

SQL views area unit used as a result of they'll offer the subsequent edges / functions:

information queries area unit simplified
information quality is hidden
Flexibility is inflated - queries of views might not modification once underlying tables chagne
Security is inflated - sensitive data are often excluded from a view

How To Use the SQL produce view Statement

The SQL produce view command is employed as follows.

SQL produce view Statement Syntax

Creates a virtual table whose contents (columns and rows) area unit outlined by a question. Use this statement to make a view of the information in one or a lot of tables within the information. as an example, a view are often used for the subsequent purposes:

To focus, simplify, and customise the perception every user has of the information.

As a security mechanism by permitting users to access information through the view, while not granting the users permissions to directly access the underlying base tables.

to supply a backward compatible interface to emulate a table whose schema has modified.


CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH view_attribute [ ,...n ] ]
AS select_statement

view_attribute ::=

A view is created solely within the current information. The produce view should be the primary statement in a very question batch. A view will have a most of one,024 columns.

When querying through a view, the information Engine checks to create positive that each one the information objects documented anyplace within the statement exist which they're valid within the context of the statement, which information modification statements don't violate any information integrity rules. A make certain fails returns a blunder message. A winning check interprets the action into Associate in Nursing action against the underlying table or tables.

If a view depends on a table or view that was born, the information Engine produces a blunder message once anyone tries to use the view. If a replacement table or view is made and therefore the table structure doesn't modification from the previous base table to exchange the one born, the view once more becomes usable. If the new table or view structure changes, the view should be born and create.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: