CREATE VIEW

---

CREATE VIEW in SQL Server

CREATE VIEW keyword creates a view. Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in such a way as you use them in a SQL SELECT query.

Example

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |
|  3 | kaulik  |  23 | Kota      |  2000.00 |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |
|  5 | Munny   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example to create a view from the above table. This view would be used to have customer name and age from the CUSTOMERS table.

SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS;

Now, you can query CUSTOMERS_VIEW in such a way as you query an actual table. Following is an example for the same.

SQL > SELECT * FROM CUSTOMERS_VIEW;

This would produce the following result.

+----------+-----+              
| NAME     | AGE |                                    
+----------+-----+                             
| Ranith  |  32  |               
| Krupa   |  25  |                                 
| kaulik  |  23  |                             
| Chaitan |  25  |                           
| Munny   |  24  |                       
+----------+-----+                                  

The WITH CHECK OPTION

The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT satisfy the condition(s) in the view definition.

If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following code block has an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION.

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

WITH CHECK OPTION in this case should deny the entry of any NULL values in the view's AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

Updating a view:

A view can be updated under certain conditions which are given below −

* The SELECT clause may not contain the keyword DISTINCT.

* The SELECT clause may not contain summary functions.

* The SELECT clause may not contain set functions.

* The SELECT clause may not contain set operators.

* The SELECT clause may not contain an ORDER BY clause.

* The FROM clause may not contain multiple tables.

* The WHERE clause may not contain subqueries.

* The query may not contain GROUP BY or HAVING.

* Calculated columns may not be updated.

* All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

So, if a view satisfies all the above rules then you can update the view. The following code block has an example to update the age of Ranith.

SQL > UPDATE CUSTOMERS_VIEW
   SET AGE = 35
   WHERE name = 'Ranith';

This would ultimately update the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement produces the following result.

+----+----------+-----+-----------+----------+                        
| ID | NAME     | AGE | ADDRESS   | SALARY   |                              
+----+----------+-----+-----------+----------+                                
|  1 | Ranith  |  35 | Ahmedabad |  2000.00 |                           
|  2 | Krupa   |  25 | Delhi     |  1500.00 |                        
|  3 | kaulik  |  23 | Kota      |  2000.00 |                                      
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |                                
|  5 | Munny   |  24 | Indore    | 10000.00 |                                
+----+----------+-----+-----------+----------+                                     

Same rules of UPDATE Command apply to the INSERT command also.

DELETING rows in a view

Same rules are to be applied for DELETING a view

Example

Following is an example to delete a record having AGE = 23.

SQL > DELETE FROM CUSTOMERS_VIEW
   WHERE age = 23;

This would produce the following result:

+----+----------+-----+-----------+----------+                        
| ID | NAME     | AGE | ADDRESS   | SALARY   |                              
+----+----------+-----+-----------+----------+                                
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |                           
|  2 | Krupa   |  25 | Delhi     |  1500.00 |                                                              
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |                                
|  5 | Munny   |  24 | Indore    | 10000.00 |                                
+----+----------+-----+-----------+----------+                                     

DROP a view

If you need a way to drop the view if it is no longer needed, the syntax is very simple and is given below −

DROP VIEW view_name;

Following is an example to drop the CUSTOMERS_VIEW from the CUSTOMERS table

DROP VIEW CUSTOMERS_VIEW;