patternsqlMajor
ALTER VIEW drops Index from View
Viewed 0 times
viewalterindexfromdrops
Problem
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to fail, informing me to drop the index(es) first.
Is there a setting in SQL SERVER to change this behaviour? Or is it changed in a version after SQL 2012 (SP3)?
Is there a setting in SQL SERVER to change this behaviour? Or is it changed in a version after SQL 2012 (SP3)?
Solution
There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:
ALTER VIEW can be applied to indexed views; however, ALTER VIEW
unconditionally drops all indexes on the view
ALTER VIEW can be applied to indexed views; however, ALTER VIEW
unconditionally drops all indexes on the view
Context
StackExchange Database Administrators Q#260901, answer score: 21
Revisions (0)
No revisions yet.