Creating Views

You can create a view with SQL Enterprise Manager or by using the CREATE VIEW statement.

    To create a view

• From the Manage menu in SQL Enterprise Manager, choose Views, and then select New from the Views drop-down list.

  (For details, search for "view" in SQL Enterprise Manager Help.)

Or

• Use the CREATE VIEW statement.

  For example:

  CREATE VIEW titles_view
  AS
  SELECT title, type, price, pubdate
  FROM titles

  creates a simple view called titles_view, consisting of the title, type, price, and pubdate columns from the titles table. You'll receive a message stating that the command did not return any data, and it did not return any rows. This is fine. The view has been created.

  (For details, see the CREATE VIEW statement in the Microsoft SQL Server Transact-SQL Reference.)

Before you create a view, consider the following guidelines:

• You can create views only in the current database.
• View names must follow the rules for identifiers and must be unique for each user.
• You can build views on other views and on procedures that reference views.
• You cannot associate rules, defaults, or triggers with views.
• You cannot build indexes on views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• You do not need to specify column names with the CREATE VIEW statement. SQL Server gives the columns of the view the same names and datatypes as the columns referred to in the select list of the SELECT statement. The select list can be a full or partial list of the column names in the base tables.
• You can specify column names; you must specify column names in the CREATE VIEW statement for every column in the view if:
  • Any of the view's columns are derived from an arithmetic expression, a built-in function, or a constant.
  • Two or more of the view's columns would otherwise have the same name (usually because the view definition includes a join and the columns being joined have the same name).
  • You want to give any column in the view a name different from the column from which it is derived. (You can also rename columns in the SELECT statement.) Whether or not you rename a view column, it inherits the datatype of the column from which it is derived.