The syntax used to create views in Microsoft® SQL Server™ and Oracle is similar.
| Oracle | SQL Server |
|---|---|
| CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name [(column_name [, column_name]...)] AS select_statement [WITH CHECK OPTION [CONSTRAINT name]] [WITH READ ONLY] |
CREATE VIEW [owner.]view_name [(column_name [, column_name]...)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] |
The Oracle FORCE option creates a view whether or not the view's base tables exist or the owner of the schema containing the view has privileges on them. SQL Server views require that the tables exist and that the view owner has privileges to access the requested tables(s) specified in the SELECT statement.
By default, data modification statements on views are not checked to determine if the rows affected are within the scope of the view. If all modifications should be checked, use the WITH CHECK OPTION. Oracle defines the WITH CHECK OPTION as a constraint; SQL Server does not. Otherwise, it functions the same in both.
SQL Server does not offer the WITH READ ONLY option when defining views. The same result can be obtained by granting only SELECT permission to all users of the view.
If you define a SQL Server view with an outer join and then query the view with a qualification on a column from the inner table of the outer join, the results can differ from what is expected. All rows from the inner table are returned. Rows that do not meet the qualification show a null value in the appropriate columns for those rows.