SQL Server: VIEW

Learn how to create, update, and drop VIEWS in SQL Server (Transact-SQL) with syntax and examples.

What is a VIEW in SQL Server?

A VIEW, in essence, is a virtual table that does not physically exist in SQL Server. Rather, it is created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW statement in SQL Server (Transact-SQL) is:

CREATE VIEW [schema_name.]view_name AS
  [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
  SELECT expressions
  FROM tables
  [WHERE conditions];

schema_name

The name of the schema that will own the view.

view_name

The name of the VIEW that you wish to create.

ENCRYPTION

It will encrypt text of the ALTER VIEW statement in sys.syscomments.

SCHEMABINDING

It ensures that the underlying table definitions can not be modified so as to affect the VIEW.

VIEW_METADATA

It will ensure that SQL Server has metadata about the VIEW.

expressions

The columns or calculations that you wish to add to the VIEW.

tables

The tables that define the VIEW. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be displayed in the VIEW.

Example

Let’s look at an example of how to use the CREATE VIEW statement in SQL Server (Transact-SQL).

For example:

CREATE VIEW prod_inv AS
  SELECT products.product_id, products.product_name, inventory.quantity
  FROM products
  INNER JOIN inventory
  ON products.product_id = inventory.product_id
  WHERE products.product_id >= 1000;

This SQL Server CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. The view would be called prod_inv.

You can now query the SQL Server VIEW as follows:

SELECT *
FROM prod_inv;

Update VIEW

You can modify the definition of a VIEW in SQL Server without dropping it by using the ALTER VIEW Statement.

Syntax

The syntax for the ALTER VIEW statement in SQL Server (Transact-SQL) is:

ALTER VIEW [schema_name.]view_name AS
  [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
  SELECT expressions
  FROM tables
  WHERE conditions;

Example

Here is an example of how you would use the ALTER VIEW Statement in SQL Server (Transact-SQL):

ALTER VIEW prod_inv AS
  SELECT products.product_name, inventory.quantity
  FROM products
  INNER JOIN inventory
  ON products.product_id = inventory.product_id
  WHERE products.product_id >= 500
  AND products.product_id <= 1000;

This ALTER VIEW example would update the definition of the VIEW called prod_inv without dropping it in SQL Server. The VIEW must exist for you to be able to execute an ALTER VIEW command.

Drop VIEW

Once a VIEW has been created in SQL Server, you can drop it with the DROP VIEW Statement.

Syntax

The syntax for the DROP VIEW statement in SQL Server (Transact-SQL) is:

DROP VIEW view_name;

view_name

The name of the view that you wish to drop.

Example

Here is an example of how to use the DROP VIEW Statement in SQL Server (Transact-SQL):

DROP VIEW prod_inv;

This DROP VIEW example would drop/delete the VIEW called prod_inv in SQL Server (Transact-SQL).