Click here to Skip to main content
13,287,658 members (45,214 online)
Click here to Skip to main content
Add your own
alternative version

Stats

38.6K views
10 bookmarked
Posted 16 Nov 2010

Introduction to SQL Server Views

, 16 Nov 2010
Rate this:
Please Sign up or sign in to vote.
Introduction to SQL Server Views

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:

EmpIdEmpNameEmpLoginEmppasswordEmploymentDate
1Vivek JohariVivekVikJoh29/01/2006
2Virender SinghVirenderVirender06/02/2007
3Raman ThakurRamanRaman14/05/2007
4Uma Dutt SharmaUmaUma30/03/2008
5Ravi Kumar ThakurRaviRavi30/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:

EmpIdEmpNameEmploymentDate
2Virender Singh06/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:

EmpIdProjectname
1Abcbank
2AtoZfinancialsol
3learningsystem
4ebooksystem
5AtoZfinancialsol

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.

License

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

Share

About the Author

Vivek Johari
Technical Lead
India India
I am currently working as a Analyst and have around 7.5 years of experience in database.

Degree:-
Master Degree in Computer(MCA)

Work experience:-
Designing of the database.
Database Optimization.
Writing Complex Stored Procedures,Functions,Triggers etc.
Designing and developing SSIS & DTS packages.
Designing SQL Reports using SSRS.
Database Server Maintenance.

Certification:-
Microsoft certified Sql DBA in Sql server 2008 (MCTS).
Microsoft certified BI professional in Sql server 2008 (MCTS).
Oracle certified profession DBA in ORACLE 10g (OCP)
certified profession DBA in ORACLE 9i (OCP)

My other publication
Technical Blog:- Technologies with Vivek Johari

Moderator and Blogger at BeyondRelational.com

Guest Author and Blogger at sqlservercentral.com

You may also be interested in...

Pro

Comments and Discussions

 
QuestionYOUR MATERIAL IS VERY GOOD BUT Pin
Member 1045866020-Oct-14 22:15
memberMember 1045866020-Oct-14 22:15 
QuestionNice Article. Pin
Member 1080797616-Jun-14 6:37
memberMember 1080797616-Jun-14 6:37 
GeneralMy vote of 5 Pin
V.S.Saini29-Aug-13 2:29
memberV.S.Saini29-Aug-13 2:29 
GeneralRe: My vote of 5 Pin
Vivek Johari9-Sep-13 2:27
memberVivek Johari9-Sep-13 2:27 
GeneralMy vote of 3 Pin
sandy199016-Jul-13 1:16
membersandy199016-Jul-13 1:16 
GeneralRe: My vote of 3 Pin
Vivek Johari9-Sep-13 2:29
memberVivek Johari9-Sep-13 2:29 
GeneralMy vote of 5 Pin
seanmir23-Dec-12 23:51
memberseanmir23-Dec-12 23:51 
GeneralRe: My vote of 5 Pin
Vivek Johari9-Sep-13 2:30
memberVivek Johari9-Sep-13 2:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171207.1 | Last Updated 16 Nov 2010
Article Copyright 2010 by Vivek Johari
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid