Post
Ask Question

Learning Views in SQL

Introduction  

Let us first understand what a view is. Views are a subset of a table or multiple tables. A view does not form part of the physical schema. It's a virtual table computed or collated dynamically from data in the database when the view is requested. It contains rows and columns, just like a real table.

A view limits the exposure of data to the user by restricting access to specified rows and columns.

Views provide data in a simplified form by hiding its complexity. The database contains only the definition of a view and not all the data so they take very less space for storage.

The following are the key points to be noted about views:
  1. Multiple views can be created on the one table.
  2. Views can be defined as read-only or updatable.
  3. Views can be indexed for better performance.
  4. Insert, update, delete can be done on an updatable view.

The following is the code snippets to create, update and delete views.

Create View

  1. CREATE VIEW view_name AS  
  2. SELECT column_name(s)  
  3. FROM table_name  
  4. WHERE condition  
  5. WITH CHECK OPTION; --This line of code is optional  
Update View
  1. CREATE OR REPLACE VIEW view_name AS  
  2. SELECT column_name(s)  
  3. FROM table_name  
  4. WHERE condition  
Delete View
  1. DROP VIEW view_name  
Updatable View

A view can be updated only if the following things are not used in the view:
  1. DISTINCT
  2. Summary functions
  3. Set functions
  4. Set operators
  5. ORDER BY clause
  6. Multiple tables
  7. Subqueries in WHERE clause
  8. GROUP BY or HAVING
  9. Calculated columns

The same rules are applicable for inserts. Also all NOT NULL columns from the base table must be included in the view in order for the INSERT operation to work.

The Insert/Update/Delete syntax for the view is similar to normal Insert/Update/Delete syntax of a table.

WITH CHECK OPTION

This option is as for the CREATE VIEW statement option. This ensures that all INSERT/UPDATE operations satisfy the conditions in the view. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

Readonly Views

We can make views readonly using the following technique:

  1. alter view viewname  
  2. as  
  3. select col1,col2 from tablename  
  4. union all  
  5. select 0,0 where 1 =0  
Also by writing instead of triggers on views we can ensure that the view is readonly.

Example

Execute the following table script.
  1. Create table Employee  
  2. (  
  3. EmployeeID int,  
  4. EmployeeName varchar(50)  
  5. )  
Once the table is created insert some records into the table.

Insert into Employee values(1,'Richard Beckinsale'),(2,'Mathew Baynton'),(3,'John Gregson')

Now we have our data ready so we will create a view. Use the following procedure.
  1. Create View vwEmployee  
  2. as  
  3. select * from Employee  
Execute the preceding code. Execute the following command in which we are fetching the data from the view instead of the table.
  1. select * from vwEmployee  


The records present in the table are now in the view.

We can make a View readonly also. Just have look at the following procedure.

The following code shows how to make a view readonly.
  1. alter view vwEmployee  
  2. as  
  3. select EmployeeID,EmployeeName from Employee  
  4. union all  
  5. select 0,0 where 1 =0  
Now the view is a readonly. We will try to insert records in the view. Execute the following SQL command.
  1. insert into vwEmployee values(1,' Helen Mirren'),(2,'Ricci Harnett'),(3,'Dani Harmer')  


As expected, since it is a readonly view, it is not allowing us to insert records into the view.


Similar Articles