Views In MS SQL

Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table.We can make index, trigger on view.

In Sql Server we make views for security purpose since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make view. One more advantage of Views is,data abstraction since the end user is not aware of all the data present in database table.

Syntax for View

CREATE VIEW view_name
AS
select_statement []

Views are of two types

1. System defined view - System defined Views are predefined Views that already exist in the Master database of Sql Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user defined database.

2.User defined view - User defined view are divided into two types

1. Regular view - Regular 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.

Syntax for Create View

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

The above syntax is described as below :
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 insys.syscomments.

SCHEMABINDING - It ensures that the underlying table definitions cannot 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.

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

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;

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

Syntax for Create Updatable View

ALTER VIEW[schema_name.]view_name AS
[WITH{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}
SELECTexpressions
FROMtables
WHEREconditions;

Example

ALTER VIEW prod_inv AS
SELECTproducts.product_name,inventory.quantity
FROMproducts
INNERJOINinventory
ONproducts.product_id=inventory.product_id
WHEREproducts.product_id>=500
ANDproducts.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

DROP VIEW view_name;

Example

DROP VIEW prod_inv;

This DROP VIEW example would drop/delete the VIEW called prod_inv in SQL Server.

I hope this article will help you.