T-SQL 101: Views

Up until this point, I’ve covered how to manipulate data stored in physical tables. Now I’ll introduce you to querying data represented by virtual tables. These virtual tables are SQL constructs known as views.

Unlike conventional physical tables, views don’t contain data. A view is merely a stored SQL statement that, when executed, generates a result set. When you query a view, you’re really querying the result set of a previously defined query. By the end of this lesson, you’ll have a firm grasp of views and how to create them. You’ll also understand the advantages and disadvantages of using them.

The Prerequisites
To run the examples in this lesson, make sure your MyDB database contains the following tables:

• The Employee table created in Lesson 3
• The Movie and Genre tables created in Lesson 5
• The revised MovieReview table created in Lesson 6 (and not the original MovieReview table created in Lesson 4)
• The MovieReviewSummary table created in Lesson 6

If you haven’t created these tables, you’ll find the code in the 99765.zip file. To download this file, click the 99765.zip link at the top of this page.

How to Create Views
To create a view, you first need to define a SELECT statement. The SELECT statement doesn’t have be a simple query of one table. It can be as complex as you need it to be. For example, you can join multiple tables or use functions to create computed columns. You can even include other views in the SELECT statement.

However, there are a few items you can’t include in a SELECT statement used to define a view. As noted in the SQL Server 2005 Books Online (BOL) documentation at msdn2.microsoft.com/en-us/library/ms187956
.aspx, you can’t include

• COMPUTE or COMPUTE BY clauses
• ORDER BY clauses unless there’s also a TOP clause
• INTO clauses
• OPTION clauses
• References to temporary tables or table variables

After you have a SELECT statement defined, you need to use the CREATE VIEW command. The basic syntax is

CREATE VIEW DatabaseName.SchemaName.ViewName

(  ColumnName1,  ColumnName2,  ColumnName3\[,...n] ) AS --Your SELECT statement

In the DatabaseName.SchemaName.View-Name segment, DatabaseName is the name of the database in which the new view will reside and Schema-
Name is the schema to which the table will belong. (If you’re unfamiliar with schemas, see Lesson 6.) The DatabaseName and SchemaName arguments are optional. If you don’t include the DatabaseName argument, the view will be created in the current database. If you don’t include the SchemaName argument, the table will be owned by the dbo schema, which is the default schema of the database owner (DBO). The examples that follow assume the current database is MyDB and you’re the DBO, so they don't include the optional Database-Name and Schema-Name arguments.

You use the View-Name argument to specify the name of the view you want to create. View names can be as long as 128 characters and must follow the rules of identifiers. (If you’re unfamiliar with these rules, see the SQL Server 2005 BOL documentation at msdn2.microsoft.com/en us/library/ms175874.aspx.) The ViewName argument is required.

Inside the parentheses is where you can list the names of the view’s columns. Alternatively, you can specify the names within the SELECT statement, as the following examples show. Like column names in tables, column names in views are limited to 128 characters, must be unique, and must follow the identifier rules.

Let’s create a view with the SELECT statement that was used to create the MovieReviewSummary table in Lesson 6. To begin, execute Listing 1’s code in the query window in SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer. In Listing 1, notice that I broke the code into two sections called batches. A batch is a series of one or more related T-SQL statements that are submitted to SQL Server for execution. SQL Server executes the commands in each batch sequentially from top to bottom. Each batch is separated by the GO keyword. In this case, the first batch causes the vMovieReview-
Summary view to be dropped if it already exists in the current database. The second batch contains the command that creates the view.

Continue to page 2