Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Using Views to Simplify Data Access in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments   |   Related Tips: More > Views

Problem
One challenge that just about everyone is faced with is ever changing database schemas.  From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change.  In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?

Solution
The simple solution here is to use Views.  Views allow you to predefine what query results will look like, enable you to pre-join your tables as well as allow you to mask any sensitive data that you don't want people to access. Basically a view is a defined and saved query that can be used over and over again.

So in the AdventureWorks database there are several tables that allow us to get employee information.  In order to get the following query elements:

  • EmployeeID
  • Title
  • FirstName
  • MiddleName
  • LastName
  • Suffix
  • Job Title
  • Phone
  • EmailAddress
  • EmailPromotion
  • AddressLine1
  • AddressLine2
  • City
  • StateProvinceName
  • PostalCode
  • CountryRegionName
  • AdditionalContactInfo

we need to join the following tables.

  • HumanResources.Employee
  • Person.Contact 
  • HumanResources.EmployeeAddress
  • Person.Address
  • Person.StateProvince
  • Person.CountryRegion

Writing this query over and over again becomes time consuming and also there are potential issues that the query will not be constructed the same way each time.

Here is what this query would look like.  It is not that complex to write, but it would be easier to save this once and reuse the view each time instead of writing this query every time you needed this data.

SELECT 
    
e.[EmployeeID]
    
,c.[Title]
    
,c.[FirstName]
    
,c.[MiddleName]
    
,c.[LastName]
    
,c.[Suffix]
    
,e.[Title] AS [JobTitle] 
    
,c.[Phone]
    
,c.[EmailAddress]
    
,c.[EmailPromotion]
    
,a.[AddressLine1]
    
,a.[AddressLine2]
    
,a.[City]
    
,sp.[Name] AS [StateProvinceName] 
    
,a.[PostalCode]
    
,cr.[Name] AS [CountryRegionName] 
    
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    
INNER JOIN [Person].[Contact] c 
    
ON c.[ContactID] e.[ContactID]
    
INNER JOIN [HumanResources].[EmployeeAddress] ea 
    
ON e.[EmployeeID] ea.[EmployeeID] 
    
INNER JOIN [Person].[Address] a 
    
ON ea.[AddressID] a.[AddressID]
    
INNER JOIN [Person].[StateProvince] sp 
    
ON sp.[StateProvinceID] a.[StateProvinceID]
    
INNER JOIN [Person].[CountryRegion] cr 
    
ON cr.[CountryRegionCode] sp.[CountryRegionCode];

To create the view for the above query the syntax is as simple as the below code.

CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    
e.[EmployeeID]
    
,c.[Title]
    
,c.[FirstName]
    
,c.[MiddleName]
    
,c.[LastName]
    
,c.[Suffix]
    
,e.[Title] AS [JobTitle] 
    
,c.[Phone]
    
,c.[EmailAddress]
    
,c.[EmailPromotion]
    
,a.[AddressLine1]
    
,a.[AddressLine2]
    
,a.[City]
    
,sp.[Name] AS [StateProvinceName] 
    
,a.[PostalCode]
    
,cr.[Name] AS [CountryRegionName] 
    
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    
INNER JOIN [Person].[Contact] c 
    
ON c.[ContactID] e.[ContactID]
    
INNER JOIN [HumanResources].[EmployeeAddress] ea 
    
ON e.[EmployeeID] ea.[EmployeeID] 
    
INNER JOIN [Person].[Address] a 
    
ON ea.[AddressID] a.[AddressID]
    
INNER JOIN [Person].[StateProvince] sp 
    
ON sp.[StateProvinceID] a.[StateProvinceID]
    
INNER JOIN [Person].[CountryRegion] cr 
    
ON cr.[CountryRegionCode] sp.[CountryRegionCode];

The only difference between the first set of code and the second set of code is:

CREATE VIEW [HumanResources].[vEmployee] AS

This statement is telling SQL Server to create a view called HumanResouces.vEmployee as the query that follows.  Once this view has been created instead of writing this query every time we can now use the view as follows or any query as long as the columns exist in the query.

SELECT FROM HumanResources.vEmployee

This is much easier than having to rewrite the query every time.  In addition, we are now guaranteed that the result set and the joins will always be consistent.

Another advantage of using views is that the underlying tables can change and the view will still work, granted you did not drop columns that the view was using.  But even if you did drop a column in a table you could change the view to return a static value and your application would continue to work without having to make any changes to the application.  Although, this depends on the data and the application.

As mentioned above you can grant access to the view and not the table and therefore you can mask certain columns that have sensitive data that you need to store, but do not want everyone to access.

Views can also be joined to other tables or views.  But be careful with this, because things can get quite complex to track down issues if you have a bunch of views and start joining views to views.

In addition, you can update views directly and also create indexed views for better performance.  There are some constraints as to what you can update in a view, but this can be done.

Next Steps

  • Take advantage of all the features that SQL Server offers.  Views are nothing new, but can simplify tasks that are repeated over and over again.
  • Look for areas in your database where views can be useful
  • Enterprise Manager and Management Studio provide query builds that you can use to create these queries and then save them as views.


Last Update: 10/5/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Top Ten

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.