ALTER VIEW

ALTER VIEW#

Redefines an existing view with a new query.

Syntax#

Arguments#

view name: The view to redefine.
select: The new SELECT query that defines the view.

Altering a materialized view#

When a view has an active materialized copy, Querona protects that copy from an implicit reset. An ALTER VIEW is applied automatically only when both of the following are true:

the new query is a recognized, representation-only form of the stored query; and
it produces the same output columns, in the same order, with the same types.

For an automatically accepted change, Querona stores the submitted SQL text and continues to use the existing materialized copy. Statistics, column identities, and the incremental-load marker are retained.

The following table describes the behavior for a view with an active materialized copy when no explicit invalidation was requested.

Change	Result
Whitespace, comments, keyword or identifier casing	Allowed; the materialized copy is retained.
Quoting, optional `AS`, redundant parentheses	Allowed; the materialized copy is retained.
Rename an existing unambiguous table or CTE alias	Allowed; the materialized copy is retained.
Add or remove a table-source alias	Rejected; request explicit invalidation.
Reuse an alias in another scope, or collide with a fixed source name	Rejected; request explicit invalidation.
Add, remove, or rename an output-column alias	Rejected; this changes the view’s output schema.
Change a predicate, source, join, literal, hint, or other query semantics	Rejected; request explicit invalidation.

Note

Adding or removing a table-source alias is currently treated conservatively, even when it looks equivalent. For example, changing FROM dbo.Product to FROM dbo.Product AS p is not automatically accepted. Qualifying a column with the new alias, such as changing Id to p.Id, is also rejected. This avoids retaining a materialized copy unless Querona can prove that every reference still binds to the same source and column. See Feature support for the feature-support overview.

To apply a substantive change deliberately, set the session-scoped invalidation option on the same connection as the ALTER VIEW statement:

EXEC sp_set_session_context
    N'querona.alter_view_materialization',
    N'invalidate';

ALTER VIEW dbo.ActiveProduct AS
SELECT p.Id, p.Name, p.Price, p.Sku
FROM dbo.Product AS p
WHERE p.Discontinued = 0;

EXEC sp_set_session_context
    N'querona.alter_view_materialization',
    NULL;

invalidate authorizes the legacy reset: Querona replaces the view metadata and makes the materialized copy unavailable until it is loaded again. Clear the session value immediately after the intended statement so it cannot authorize a later change accidentally. Any other nonempty value is rejected.

Views without an active materialized copy retain the normal ALTER VIEW behavior. While a full load of a view’s materialization is running, an ALTER VIEW that changes the definition is rejected: the load writes the cached copy from the definition in force when it started, so the statement would leave the copy matching neither definition. invalidate does not waive this - wait for the load to finish, or cancel it, and run the statement again.

Examples#

ALTER VIEW dbo.ActiveProduct AS
SELECT Id, Name, Price, Sku
FROM   dbo.Product
WHERE  Discontinued = 0;