User Defined Views – Part 2

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:

  1. To simplify or customize the perception each user has of the database.
  2. Security mechanism to grant users access to the view, not the underlying base tables.
  3. 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.

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.

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.

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.

Last but not least, we should restore the user defined view before leaving for the day.

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.

Views – Union All Output

Views – Do’s and Don’ts Examples

Related posts

Leave a Comment