Views

•  A view is nothing more than a saved SQL Query.
• A view Can also be considered as a virtual table.

Advantages of using views:

1. Views can be used to reduce the complexity of the database schema, for non IT users. The view, hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.

2. Views can be used as a mechanism to implement row and column level security.

Row Level Security: For example, I want an end user, to have access only to IT Department employees. If I grant him access to the Employees and Departments tables, he will be able to see, every department employees. To achieve this, I can create a view, which returns only IT Department employees, and grant the user access to the view and not to the underlying table.

Column Level Security: Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to this views, rather than the base tables.

3. Views can be used to present only aggregated data and hide detailed data.

Create a  SQL View:

CREATE VIEW ViewName

AS  

Select column1, Column2...Column N

From tables  

Where conditions;

SQL VIEW for DML (Update, Delete and Insert) queries

We can use SQL VIEW to insert, update and delete data in a single SQL table. We need to note the following things regarding this.

1. We can use DML operation on a single table only

2. VIEW should not contain Group By, Having, Distinct clauses

3. We cannot use a subquery in a VIEW in SQL Server

4. We cannot use Set operators in a SQL VIEW

Alter View:

    To modify a view - ALTER VIEW statement 

Drop View:

    To Drop a view - DROP VIEW vWName 

To look at view definition:

    sp_helptext vWName