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).
