As you might know a View in SQL is nothing but a SELECT statement, sometimes more than one too, that is stored in the Data Dictionary and is executed when you want to use it. It does not have any physical memory for data; the data that you see in a View comes from tables that view is accessing. So why do we create a View, most of the time for reasons of security, to limit the direct access or to have restricted access to the data by user. This helps in unwanted modification of business sensitive data. Also Views are created for various uses of data analysis and mining. But following caution should be taken while creating a View;

n       No Indexes should be created on Views,

n       If you specify a TOP n or TOP m PERCENT option, then only you can specify an ORDER BY clause otherwise NO.

n       When referencing multiple tables make use of indexes for faster access.

Here is the syntax:

CREATE VIEW <view-name> [( <column alias name1>,... ) ] AS

LOCK [TABLE | VIEW | ROW ] FOR | IN [READ | WRITE | ACCESS ] MODE NOWAIT     SELECT    [DISTINCT | ALL ] TOP <n|m> PERCENT [WITH TIES]

              <column-name> [AS <alias-name> ]

          [ , <column-name> [AS <alias-name> ] ]

FROM <table-name> <JOIN CONDITION | SUB QUERY>

[ WHERE <search condition>

            GROUP BY <grouping specification>     HAVING <condition >]

[ WITH CHECK OPTION ]     ORDER BY <expression> [ASE | DESC];

Use the clause WITH CHECK OPTION for “updatable Views” which helps in restricting the rows that can be updated in the table by an INSERT or UPDATE statement to satisfy the condition in WHERE clause. In other words, use this clause if you want to insert rows that satisfies the WHERE clause, present in the View definition. If you do not specify this clause, rows would be inserted ignoring the WHERE clause but base table checks and constraints remain enforced.

Popularity: 38% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links