User Defined Views – Part 1

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 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.

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.

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.

User Defined Views – Output

User Defined Views – Example

Related posts

Leave a Comment