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 SQL snippet below, drops the view if it exists, creates a new stub function to demonstrate column names, and alters the function to be identical to the original example on MSDN.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<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 -- Create stub view (abstracting columns) CREATE VIEW [HumanResources].[vHireDate] (Eenie, Meenie, Miney, Mo) AS SELECT 1 AS Fee, 2 AS Fi, '3' AS Fo, '4' AS Fumb GO -- Show the funny data SELECT * FROM [HumanResources].[vHireDate] GO </span> |
I was thinking about children’s counting rhymes and fairy tales when I created the stub example.
Please note, the SELECT statement uses column alias to give the constants names. However, these aliases are overwritten by the view’s column definition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span style="color: #008000;">-- Alter view to msdn example 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 -- Example use of the view SELECT * FROM [HumanResources].[vHireDate] WHERE FirstName like 'J%' ORDER BY FirstName GO </span> |
The final ALTER VIEW statement creates an object that is equivalent to the one on MSDN with some literary license taken to names and formatting. A view can be used in TSQL anywhere a table name can be supplied.
In review, a User Defined View is a compiled TSQL query based on one or more tables. Therefore, it should run a little faster since the query plan is probably in the cache. However, I would not use a VIEW for this reason alone.
The major benefit of a view is data abstraction and security. From our funny children’s example, I can abstract the underlying tables defined by the VIEW from the end user. This allows for modifications of the underlying schema in the future.
Next time, I will be discussing TSQL statements that can and can not be used when creating a view.
