View in Sql Server
A view is nothing but a SQL statement that is stored in
the database with an allied name. A view is truly an arrangement of a table in
the form of a SQL query. A view can be created from one or many tables which
depends on the written SQL query to create a view.
Views, which are kind of virtual tables, allow users to
do the following:
• Structure
data in a way that users or classes of users find natural or intuitive.
• Limit
access to the data such that a user can see and modify exactly what they need.
• Summarize
data from various tables which can be used to produce reports.
How to create view in DB:
Database views are created using the CREATE VIEW
statement.
The basic CREATE VIEW syntax is as follows:
CREATE VIEW view_name AS
SELECT columns .....
FROM table_name
WHERE
[condition];
You can have multiple tables in SELECT statement in very
like you use them in normal SQL SELECT query.
WITH CHECK OPTION:
The WITH CHECK OPTION is a CREATE VIEW statement option.
The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs
satisfy the condition(s) in the view definition.
If they do not satisfy the condition, the UPDATE or INSERT
returns an error.
The following is an example of creating same view
CUSTOMERS_VIEW with the WITH CHECK OPTION:
CREATE VIEW Emp_Details_View AS
SELECT name, Address, PhoneNo, Salary, age
FROM Emp_Details
WHERE age IS NOT NULL WITH CHECK OPTION;
The 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.
Update a View:
A view can be updated under certain conditions:
• SELECT clause
may not contain the keyword DISTINCT.
• SELECT
clause may not contain summary functions.
• SELECT
clause may not contain set operators.
• SELECT
clause may not contain an ORDER BY clause.
• FROM clause
may not contain multiple tables.
• WHERE
clause may not contain subqueries.
• SELECT
clause may not contain set functions.
• 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.
Inserting Rows into a View:
Rows of data can be inserted into a view. The same rules
that apply to the UPDATE command also apply to the INSERT command.
Here we cannot insert rows in Emp_Details_VIEW because we
have not included all the NOT NULL columns in this view, otherwise you can
insert rows in a view in similar way as you insert them in a table.
Deleting Rows into a View:
Rows of data can be deleted from a view. The same rules
that apply to the UPDATE and INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE=
22.
DELETE FROM Emp_Details_VIEW WHERE age = 22;
Dropping Views:
Where you have a
view, you need a way to drop the view if it is no longer needed. The syntax is
very simple as given below:
DROP VIEW view_name;
