Oracle Vs MS-Sql : Views

In continuation with the discussion on Oracle vs. MSSQL, today I will address the views in two databases.

There are two different types of views – simple views and materialized views. Views are created to hide the complexity of SQL statements to the end-applications and querying agents. Views can include queries to multiple tables, rules and transformations, and even aggregations.

Simple views do not contain data, just SQL statements which use the underlying tables to fetch data while executing. Materialized views persist data in and queries to underlying tables are not made unless the view is refreshed explicitly.

With simple views, it is also possible to perform an update statement into the base table. In such case the following operators should not be present in the SQL inside the view.

1. Set Operators (INTERSECT, MINUS|EXCEPT, UNION, UNION ALL)
2. DISTINCT
3. GROUP BY
4. ORDER BY
5. JOIN
6. INTO

The updateable views should not contain subqueries as well.

The syntax to create view in Oracle is:

CREATE OR REPLACE VIEW view_name AS SELECT * FROM xyz;

The syntax to create view in MSSQL is:

CREATE VIEW view_name AS SELECT * FROM xyz
ALTER VIEW view_name AS
SELECT column_name FROM xyz

For more information about views in Oracle use
              SQL/PLSQL for oracle 9i by P.S.Deshpande
              Oracle9i PL/SQL Programming by Scott Urman
              Oracle PL/SQL Programming, Third Edition by Steven Feuerstein
For more information about views in MS-SQL use
              Inside Microsoft SQL Server 2005/2008 by Kalen Delaney
I will discuss more differences between Oracle and MSSQL in the subsequent posts.