A view is a named virtual table that is defined by a query and used as a table. Unlike permanent tables, a view has no physical representation of its data unless you create an index on it. Whenever you issue a query against a nonindexed view, SQL Server in practice has to access the underlying tables. Unless specified otherwise, the discussions in this chapter involve nonindexed views.

When you create a view, you specify a name for the view and a query. Microsoft SQL Server stores only metadata information about the view, describing the object, its columns, security, dependencies, and so on. When you query a view–by retrieving or modifying data–the query processor replaces a view reference with its definition; in other words, the query processor "expands" the view definition and generates an execution plan accessing the underlying objects.

Views play important roles in the database. One of the more valuable uses of views is as an abstraction mechanism. For example, you can use views to make it easier to provide a more or less normalized picture of the underlying data, where appropriate, without changing the normalization of the actual data. You can use them to simplify your solutions by applying a modular approach–solving complex problems one step at a time. You can use views as a security layer (to some degree) by granting access to filtered or manipulated data only through views, and not directly against the base tables (provided that the schema of the view and the schema of the underlying objects are the same).

Views can also play a performance role if you create an index on them. Creating a clustered index on the view materializes its data on disk, giving the view a physical dimension, as opposed to its normal virtual role. I’ll describe indexed views later in the chapter in a dedicated section. For now, the important point is that without an index, a view typically has no special performance impact–negative or positive.

As with any other table expression (such as a derived table, common table expression [CTE], or inline table-valued user-defined function [UDF]), the query defining the view must meet three requirements:

An ORDER BY clause without TOP or FOR XML specification is not allowed in the query defining the view because a view is supposed to represent a table. A table is a logical entity that has no order to its rows–as opposed to a cursor, which is a physical object that does have order to its records. Naturally, all columns must have names in a valid table, and the names must be unique. You can assign column names to the target columns of a view either in parentheses following the view name or as inline column aliases following the individual expressions.

As an example, run the following code to create the VCustsWithOrders view:

SET NOCOUNT ON;
USE Northwind;
GO
IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
  DROP VIEW dbo.VCustsWithOrders;
GO
CREATE VIEW dbo.VCustsWithOrders
AS

SELECT CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Country, Phone, Fax
FROM Customers AS C
WHERE EXISTS
  (SELECT * FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID);
GO

This view contains customers that placed orders.

Note

If you attempt to run this code in SQL Server 2000, it will fail because of the existence of the semicolon at the end of the CREATE VIEW statement. To create the view in SQL Server 2000, simply drop the semicolon. The semicolon is an ANSI requirement, though T-SQL never required it in the past. In SQL Server 2005, you’re required to use a semicolon only in particular cases, for example, before a WITH clause that defines a CTE to avoid ambiguity (because the WITH clause can be used for other purposes as well). Otherwise, the use of a semicolon is optional. However, because a semicolon is an ANSI requirement, it might be a good idea to start getting used to it.

The view’s query uses the EXISTS predicate to return customers that have at least one order in the Orders table.

Tip

As an aside, even though the use of * is generally a bad practice, you can use it safely with the EXISTS predicate. The optimizer knows that the EXISTS predicate does not refer to a particular attribute from the row. Rather, it cares only about existence; therefore, it ignores the SELECT list altogether. You can deduce this by examining execution plans for such queries and noticing that if there’s an index on the filtered column (O.CustomerID in the preceding example), it will be used and there won’t be additional lookup operations. Another way to demonstrate that the SELECT list is completely ignored is by specifying expressions that would normally cause an error, for example:

IF EXISTS(SELECT 1/0) PRINT 'no error';

This code runs with no error, demonstrating that SQL Server didn’t evaluate the expression. If SQL Server had evaluated the expression, you would have received an error.

The following sections will explore various aspects of views in more detail, starting with the reasoning behind disallowing an ORDER BY clause without a TOP or FOR XML specification in the view’s query.