Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

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     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.