solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

SQL Sentry, Inc. - SQL Sentry Performance Advisor

SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.

Learn more!

Using Views to Simplify Data Access in SQL Server

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

Free SQL Server Learning - Making the most out of SQL Server Agent

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

Become a paid author

Comments and Feedback:

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement.

NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now

What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services.

Join the over million SQL Server Professionals who get their issues resolved daily.

Optimizing SQL Server performance can be a daunting task. Or is it?