SQL: Create a View

On By Ben Larson Ph.D.In MS SQL Server

I am working with MS SQL Server and the AdventureWorks2012 database in this lesson. If you want to play along, but do not have either installed on your computer, check out the following lessons to get you up and running:

Views

A view is a virtual table created in SQL that pulls information from existing tables in the database. To understand the need for a view, imagine you are the DBA for AdventureWorks constantly being asked to produce email addresses for employees. You are being asked this because whoever designed the front end of the database that your users interact with, left this vital piece of information out.

Finding an employee’s email address is simple enough. The SQL query below is all you need.

SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID

Just add a where statement depending on if you are searching by first or last name.

But what if you are getting tired of having to create this query over and over every day. Well, you could make this query a permanent part of your database by turning it into a view.

The syntax for creating a view is simple. Just add Create View [NAME] and AS

create view HumanResources.vEmail
as 
SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID

go

After you run this query, you can now find your view in the Object Explorer

view.jpg

You can now query the view just like you would any other table.

 select *
 from HumanResources.vEmail
 where LastName like 'Ken%'

Views are great especially when dealing with very complex joins. Once you have figured out the proper query, you can just save it as a join and you won’t have to reinvent the wheel next time you need it.

 

Leave a Reply