Today, I want to continue talking about database objects that are associated with stored (compiled) code.
A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database.
Typical uses of a view are the following:
- To simplify or customize the perception each user has of the database.
- Security mechanism to grant users access to the view, not the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed.
Like most Data Definition Language (DDL) constructs, a user defined view has three operations associated with it: CREATE VIEW, ALTER VIEW and DROP VIEW.
I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk.
The TSQL snippet drops the [HumanResources].[vHireDate] view if it exists.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<span style="color: #008000;">-- Use the correct database USE [AdventureWorks2012] GO -- Delete existing view IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HumanResources].[vHireDate]') AND [type] in (N'V') ) DROP VIEW [HumanResources].[vHireDate] GO </span> |
The first lesson is that views are based upon a single simple or complex TSQL statement and to use the ORDER BY clause in a view, we need to specify a TOP 100 filter. Otherwise, the algebraic parser will stop the statement execution with an error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<span style="color: #008000;">-- -- Lesson A - use top 100 with order by -- -- Alter view, order by clause CREATE VIEW [HumanResources].[vHireDate] AS SELECT TOP 100 p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ORDER BY p.FirstName; GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO </span> |
The second lesson is that view can not contain the following: reference to temporary tables, the INTO clause for table creation or the OPTION clause for query hints. The example below tries to break all three restrictions. This results in a error produced by the algebraic parser.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<span style="color: #008000;">-- -- Lesson B - Certain constructs not allowed with views. -- -- Save employee table as local temp table SELECT * INTO #tmpEmployee FROM HumanResources.Employee; GO -- Alter view, [into], [option], [#tmp] are syntax errors ALTER VIEW [HumanResources].[vHireDate] AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate -- INTO #tmpBadIdea FROM -- #tmpEmployee e HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID -- OPTION (MERGE JOIN); GO </span> |
The third lesson is that the UNION key word should be used to join multiple result sets. The example below returns all people who’s first name starts with D or J.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<span style="color: #008000;">-- -- Lesson C - Use union to join result sets -- -- Alter view, just people whos names start with D or J ALTER VIEW [HumanResources].[vHireDate] AS -- J names SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE P.FirstName LIKE 'J%' UNION ALL -- D names SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE P.FirstName LIKE 'D%' GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO </span> |
Last but not least, we should restore the user defined view before leaving for the day.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span style="color: #008000;">-- Restore the definition ALTER VIEW [HumanResources].[vHireDate] AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID; GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO </span> |
To recap today’s talk, views can be defined by one simple or complex TSQL statement. To join result sets in the view, use the UNION key word. Certain keywords and objects are restricted from usage when defining a view. Please look at the Books On Line (BOL) or MSDN articles for more information.
Next time, I will be discussing options that can used when creating a view. For each option, I will show an example that will demonstrate why to use it in the first place.
