alter-materialized-view-transact-sql.md

title	ALTER MATERIALIZED VIEW (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	07/03/2019
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer	jrasnick
ms.technology	data-warehouse
ms.topic	language-reference
f1_keywords	ALTER_VIEW_TSQL ALTER VIEW
dev_langs	TSQL
helpviewer_keywords	indexed views [SQL Server], modifying views [SQL Server], modifying modifying views ALTER VIEW statement
author	XiaoyuMSFT
ms.author	xiaoyul
monikerRange	= azure-sqldw-latest || = sqlallproducts-allversions

ALTER MATERIALIZED VIEW (Transact-SQL)

[!INCLUDEtsql-appliesto-xxxxxx-xxxx-asdw-xxx-md]

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

Syntax

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

Arguments

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.

Permissions

ALTER permission on the table or view is required.

Examples

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;  

See also

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)
SQL Data Warehouse and Parallel Data Warehouse Catalog Views
System views supported in Azure SQL Data Warehouse
T-SQL statements supported in Azure SQL Data Warehouse