ALTER MATERIALIZED VIEW (Transact-SQL)

Applies to: Azure Synapse Analytics

Modifies a previously created materialized view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions.

Transact-SQL syntax conventions

ALTER MATERIALIZED VIEW [ schema_name . ] view_name
{
      REBUILD | DISABLE
}
[;]

schema_name
Is the name of the schema to which the view belongs.

view_name
Is the materialized view to change.

REBUILD
Resumes the materialized view.

DISABLE
Suspends maintenance on the materialized view while maintaining metadata and permissions.  All queries against the materialized view while in a disabled state resolve against the underlying tables.

ALTER permission on the table or view is required.

This example disables a materialized view and puts it in suspended mode.

ALTER MATERIALIZED VIEW My_Indexed_View DISABLE;  

This example resumes materialized view by rebuilding it.

ALTER MATERIALIZED VIEW My_Indexed_View REBUILD;  

Performance tuning with Materialized View
CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)
EXPLAIN (Transact-SQL)
sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_mappings (Transact-SQL)
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
Azure Synapse Analytics and Analytics Platform System (PDW) Views
System views supported in Azure Synapse Analytics
T-SQL statements supported in Azure Synapse Analytics