Replaces a view definition with a modified version.
Syntax 1 - Change the definition of a viewALTER VIEW [ owner.]view-name [ ( column-name, ... ) ] AS select-statement [ WITH CHECK OPTION ]
Syntax 2 - Change the attributes of a viewALTER VIEW [ owner.]view-name { SET HIDDEN | RECOMPILE | DISABLE | ENABLE }
ParametersAS clause The SELECT statement on which the view is based. The SELECT statement must not refer to local temporary tables. Also, query-expression can have a GROUP BY, HAVING, WINDOW, or ORDER BY clause, and can contain UNION, EXCEPT, INTERSECT, or a common table expression.
The semantics of queries dictates that the order of the rows returned is undefined unless the query combines an ORDER BY clause with a TOP or FIRST clause in the SELECT statement. If an ORDER BY clause is specified along with either a TOP or FIRST clause, then the query returns the top n rows, or the first row, respectively.
WITH CHECK OPTION clause The WITH CHECK OPTION clause rejects any updates and inserts to the view that do not meet the criteria of the view as defined by its query-expression.
SET HIDDEN clause Use the SET HIDDEN clause to obfuscate the definition of the view and cause the view to become hidden from view, for example in Sybase Central. Explicit references to the view still work.
The SET HIDDEN operation is irreversible.
RECOMPILE clause Use the RECOMPILE clause to re-create the column definitions for the view. This clause is identical in functionality to the ENABLE clause, except that it can be used on a view that is not disabled. When a view is recompiled, the database server restores the column permissions based on the column names specified in the new view definition. The existing permissions are lost when a column no longer exists after the recompilation.
DISABLE clause Use the DISABLE clause to disable the view from use by the database server.
ENABLE clause Use the ENABLE clause to enable a disabled view. Enabling the view causes the database server to re-create the column definitions for the view. Before you enable a view, you must enable any views upon which it depends.
RemarksIf you execute an ALTER VIEW statement on a view that has one or more INSTEAD OF triggers, an error is returned. You must drop the trigger before the view can be dropped or altered.
If you alter a view owned by another user, you must qualify the name by including the owner (for example, GROUPO.ViewSalesOrders). If you don't qualify the name, the database server looks for a view with that name owned by you and alters it. If there isn't one, it returns an error.
When you alter a view, existing permissions on the view are maintained, and do not have to be reassigned. Instead of using the ALTER VIEW statement, you could also drop the view and recreate it using the DROP VIEW and CREATE VIEW, respectively. However, if you do so, permissions on the view need to be reassigned.
After completing the view alteration using Syntax 1, the database server recompiles the view. Depending on the type of change you made, if there are dependent views, the database server attempts to recompile them as well. If you have made a change that impacts a dependent view, you may need to alter the definition for the dependent view as well.
If the SELECT statement defining the view contained an asterisk (*), the number of the columns in the view may change if columns have been added or deleted from the underlying tables. The names and data types of the view columns may also change.
Syntax 1 This syntax is used to alter the structure of the view. Unlike altering tables where your change may be limited to individual columns, altering the structure of a view requires you to replace the entire view definition with a new definition, much as you would for creating the view.
Syntax 2 This syntax is used to change attributes for the view, such as whether the view definition is hidden.
When you use SET HIDDEN, the view can be unloaded and reloaded into other databases. If SET HIDDEN is used, debugging using the debugger does not show the view definition, nor is it be available through procedure profiling. If you need to change the definition of a hidden view, you must drop the view and create it again using the CREATE VIEW statement.
When you use the DISABLE clause, the view is no longer available for use by the database server for answering queries. Disabling a view is similar to dropping it, except that the view definition remains in the database. Disabling a view also disables any dependent views. Therefore, the DISABLE clause requires exclusive access not only to the view being disabled, but also any dependent views, since they are disabled too.
PermissionsMust be owner of the view or have DBA authority.
Side effectsAutomatic commit.
All procedures and triggers are unloaded from memory, so that any procedure or trigger that references the view reflects the new view definition. The unloading and loading of procedures and triggers can have a performance impact if you are regularly altering views.
See also Standards and compatibilitySQL/2008 Vendor extension.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |