Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The tables referenced by the SELECT are known as the base tables for the view.

There are basically three ways to create a view in SQL Server™.

• using the Create View Wizard in Enterprise Manager
• using the Database Views node in Enterprise Manager
• using the Transact-SQL CREATE VIEW statement

For quick tests, you can design views interactively using the graphical interface in Enterprise Manager. Note, though, that using those tools may be viable only when working with SQL Server™ on a localhost.

In most cases, you will have to work with a remote production server, wherein using Enterprise Manager may not be feasible. In such cases, you can programmatically create views using Transact-SQL. This article focuses on implementing views with this method.

The CREATE VIEW statement is used to define the query for the view and save the view in the current database. In its simplest form, CREATE VIEW expects two arguments: the view name, and the SELECT query.

CREATE VIEW viewName AS query ... 

• viewName is the name given to the view, and thereafter used to reference the view.

  View names must follow SQL Server™ rules for identifiers. Names with spaces or reserved keywords must be delimited within single quotes, ex. 'my View' or a pair of brackets, ex. [my View].

• query defines the actions the view is to perform.

  The query defining the view can be any valid Transact-SQL SELECT statement, and can reference one or more tables or other views in the current or other databases. There are a few restrictions, though, on using the SELECT clause in a view definition. A CREATE VIEW statement cannot:

  • Include an ORDER BY clause, unless there is also a TOP clause in the SELECT statement.
  • Include the INTO keyword.
  • Include COMPUTE or COMPUTE BY clauses.
  • Reference a temporary table or a table variable.

NOTE: CREATE VIEW must be the first statement in a query batch.

The below code snippet shows how to define a simple view that selects data from two base tables.

CREATE VIEW [nw_Customer and Suppliers by City] 
AS 
SELECT City, CompanyName, ContactName, 'Customer' AS Relationship 
   FROM nw_Customers 
UNION SELECT City, CompanyName, ContactName, 'Supplier' 
   FROM nw_Suppliers

To create the view, we send this as a command to our database, like

SqlCommand myCmd = new SqlCommand ();
myCmd.Connection = new SqlConnection ( ConfigurationSettings.AppSettings["aspa"] );

myCmd.CommandText = "CREATE VIEW [nw_Customer and Suppliers by City] AS ... ";

myCmd.Connection.Open ();
myCmd.ExecuteNonQuery ();
myCmd.Connection.Close ();

Once defined, a view can be used by simply referencing the view name in any Transact-SQL statement, in the same way a table is referenced.

string query = "SELECT * FROM [nw_Customer and Suppliers by City]";

Customer and Suppliers by City Run Sample | View Source

There are basically two ways to programmatically modify a previously saved view:

• using DROP VIEW and recreating the view, or
• using ALTER VIEW.

DROP VIEW removes one or more views from the current database. When you drop a view, the definition and other information about the view is deleted from the system tables. All permissions for the view are also deleted.

When querying through a view, SQL Server™ checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action against the underlying table(s).

If the underlying table(s) or view(s) have changed since the view was originally created, it may be useful to drop and re-create the view. This example removes a view named titlesView.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'titlesView') DROP VIEW titlesView

ALTER VIEW modifies a previously created view without changing permissions and without affecting any dependent objects, unless the definition of the view changes in such a way that the dependent object is no longer valid.

If a view currently in use is modified by using ALTER VIEW, SQL Server™ takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, SQL Server™ deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.

For more information about the parameters used in CREATE VIEW, ALTER VIEW and DROP VIEW, see the Transact-SQL Reference in SQL Server™ Books Online.