Creating and Working with Views in a Database

Introduction

This article discusses how to create, update, and delete views in a database using SQL. Views are generally used to focus, simplify, and customize each user's perception of the database.

Views in SQL

You can use a view as a security mechanism by allowing a user to access data through the view without granting the user permission to directly access the underlying base tables of the view. You can also use views to improve performance.

A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current database or other databases. A view is a SELECT statement that's stored in the database. Find a detailed article about Views in SQL here: Detailed article on Views in SQL

Create and Using a View

To create a view, you use CREATE VIEW SQL statement. You can think of a view as a virtual table that consists only of the rows and the columns specified in its CREATE VIEW statement. The table or tables listed in the FROM clause are called the case tables for the view. Since the view refers back to the base tables, it doesn't store any data itself, and it always reflects the most current data in the base tables.

To use a view, you refer to it from another SQL statement. When you use any view in the FROM clause instead of a table, as a result, this SELECT statement extracts its result set from the virtual table that the view represents.

Because a view is stored as an object in a database, it can be used by anyone who has access to the database.

Example

--To Create a view
CREATE VIEW V_Shashi as SELECT FN_NAME,LN_NAME FROM SHASHI
 
--To Execute a View.
SELECT * FROM V_Shashi

A view can also be referred to as a viewed table because it provides a view of the underlying base tables.

Advantages of Views

The data that you access through a view isn't dependent on the structure of the database. Suppose a view refers to a table that you have decided to divide into two tables. To accommodate this change you simply modify the view, you don't have to modify any statements that refer to the view. That means users who query the database using the view don't have to be aware of the change in the database structure, and application programs that use the view don't have to be modified.

You can also use views to restrict access to a database. To do that you include just the columns and rows you want a user to have access to in the view. Then you let the user access the data only through views.

Create and Manage Views

Now you have a fair knowledge of views and how they work now let's get into a more practice version.

CREATE VIEW view_name [(column_name_1 [,column_name_1]…)]
[WITH {ENCRPTION| SCHEMABINDING| ENCRPTION, SCHEMABINDING}]
AS
Select_statement
[WITH CHECK OPTION]
 
CREATE VIEW V_Shashi as SELECT FN_NAME,LN_NAME FROM SHASHI

You use CREATE VIEW statement to create a view. The name you give the view must not be the same as the name of any existing table or view. The SELECT statement can refer to as many as 256 tables and can use any valid combination of joins, unions, or sub-queries.

You can even create a view based on another view rather than on a table, called a nested view. SQL Server views can be nested up to 32 levels. The SELECT statement for a view can't include an INTO clause and it can include an ORDER BY clause only if the TOP keyword is used. To sort the rows in a view you have to include the ORDER BY clause in the SELECT statement that uses it.

You can name the columns in view by coding a list of names in parentheses following the view or by coding the new names in the SELECT clause. A column must be named if it's calculated from other columns otherwise the name from the base table can be used. You can use WITH ENCRYPTION clause to keep users from examining the SQL code that defines the view.

You can use WITH SCHEMABINDING clause to bind a view to the database schema. Then you can't drop the tables on which the view is based or modify the tables in a way that would affect the view. If you include the WITH SCHEMABINDING clause then you can't use the all columns operator (*) in the SELECT statement. In addition, you must qualify the names of the tables and view them in the FROM clause with the name of the database owner.

You can use WITH CHECK OPTION clause to prevent a row from being updated through a view if it would no longer be included in the view. The WITH CHECK OPTION clause prevents a row in a view from being updated if that would cause the row to be excluded from the view.

How to create an updatable view?

Once you create a view, you can refer to it in a SELECT statement just like any other table. In addition, you can refer to it in INSERT, UPDATE, and DELETE statements to modify an underlying table. To do that view must be updatable.

To create an updatable view you must meet the following requirements.

The select list can't include a DISTINCT or TOP clause.
The select list can't include an aggregate function.
The select list can't include a calculated value.
The SELECT statement can't include a GROUP BY or HAVING clause.
The view can't include the UNION operator.

If a view is not updatable it's called a read-only view.

How to delete or modify a view?

To delete a view you use the DROP VIEW statement. In this statement, you simply name the view you want to delete. Like the other statements for deleting database objects, this statement deletes the view permanently.

To modify a view you can use the ALTER VIEW statement. The syntax of this statement is the same as the syntax of the CREATE VIEW statement. If you understand the CREATE VIEW statement then you won't have any trouble using the ALTER VIEW statement. Instead of using the ALTER VIEW statement to modify a view, you can delete the view and recreate it.

If you have created stored procedures triggers that depend on the view or if you have modified the permissions for the view you should know that those stored procedures, triggers, and permissions are deleted when the view is deleted. If that's not what you want you should use ALTER VIEW statement instead.

The syntax of the DROP VIEW statement

DROP VIEW view_name

The syntax of the ALTER VIEW statement

ALTER VIEW view_name [(column_name_1 [, column_name_2]…)]
[WITH {ENCRPTION| SCHEMABINDING| ENCRPTION, SCHEMABINDING}]
AS
Select_statement
[WITH CHECK OPTION]

If you delete a table, you should also delete any views that are based on that table. Otherwise, an error will occur when you run a query that refers to one of those views. To find out what views are dependent on a table display the table's dependencies.

ALTER VIEW isn't an ANSI-standard statement. Although it's supported on other SQL-based systems, its behavior on each system is different.

How to use views?

You have seen how to use views in SELECT statements to retrieve data from one or more base tables. But you can also use views to update base tables.

How to update rows through a view?

To update a view you simply name the view that refers to the table in the UPDATE statement.

How to insert rows through a view?

To insert rows through a view you use the INSERT statement as the way you use it to insert rows into a table.

INSERT INTO V_Shashi values ('shashi', 'satyam')

How to delete rows through a view?

To delete rows from a view you simply name the view that refers to the table in the delete statement.

To get the Source of view query the following information schema views.

INFORMATION_SCHEMA.VIEWS

Conclusion

This article discusses how to create, update, and delete views in a database using SQL. Reference Article: Detailed article on Views in SQL