Learning Views in SQL

Nitin

11y7.1k15100Article

SQL ViewSQL ViewsViews in SQLCreate View in SQL

15 Comments

Join the conversation! Your thoughts help the community grow.

Muhammad Ibrar7y ago
Nice. Its great article
Faiz Mirza11y ago
Just the kind of article I was looking for. Thanks Nitin
- Nitin11y ago
  Thanks Faiz Mirza :) m glad u liked it
Karthik Muthu Karuppan11y ago
good
- Nitin11y ago
  Thanks Karthik Muthu Karuppan
Yashwant Vishwakarma11y ago
Nice article!!
- Nitin11y ago
  Thanks Yashwant Vishwakarma :)
Shubham Kumar11y ago
helpful
- Nitin11y ago
  Thanks Shubham kumar
Manoj Bhoir11y ago
Well explained Nitin.
- Nitin11y ago
  Thanks Manoj Bhoir :)
Manoj Kulkarni11y ago
Very informative thankx
- Nitin11y ago
  Thanks Manoj Kulkarni :)
Rahul Kumar Saxena11y ago
Good Work Nitin Tyagi...
- Nitin11y ago
  Thanks Rahul Saxena :)

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:

Multiple views can be created on the one table.
Views can be defined as read-only or updatable.
Views can be indexed for better performance.
Insert, update, delete can be done on an updatable view.

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

Create View

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
WITH CHECK OPTION; --This line of code is optional

Update View

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Delete View

DROP VIEW view_name

Updatable View

A view can be updated only if the following things are not used in the view:

DISTINCT
Summary functions
Set functions
Set operators
ORDER BY clause
Multiple tables
Subqueries in WHERE clause
GROUP BY or HAVING
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:

alter view viewname
as
select col1,col2 from tablename
union all
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.

Create table Employee
(
EmployeeID int,
EmployeeName varchar(50)
)

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.

Create View vwEmployee
as
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.

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.

alter view vwEmployee
as
select EmployeeID,EmployeeName from Employee
union all
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.

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.