By: Greg Robidoux | Comments | Related: > 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
Summary
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
- SQL Server Management Studio provides query builders that you can use to create these queries and then save them as views.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
