ALTER VIEW drops Index from View
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
You can prevent altering an indexed view with a DDL trigger. But the implementation is a bit complex, because the DDL trigger runs after the view has been altered and the index dropped, but before the change has been committed, and you can't directly detect that the view previously had an index.
So you have to get up to some shenanigans with extended properties, placing an extended property on the view whenever an index is created on it.
EG:
create or alter trigger ddl_trig_prevent_alter_indexed_view
on database
for drop_index, create_index, alter_view
as
begin
--select eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') , eventdata()
declare @schemaName sysname, @objectName sysname, @eventType varchar(50), @targetObjectType varchar(50)
,@targetObjectName sysname, @targetObjectSchemaName sysname
declare @e xml = eventdata();
select @eventType = @e.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),
@targetObjectType = @e.value('(/EVENT_INSTANCE/TargetObjectType)[1]','varchar(50)'),
@targetObjectName = @e.value('(/EVENT_INSTANCE/TargetObjectName)[1]','sysname'),
@schemaName = @e.value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),
@objectName = @e.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')
if @eventType = 'DROP_INDEX' and @targetObjectType = 'VIEW'
begin
--print @eventType
set @targetObjectSchemaName = (select schema_name(schema_id) from sys.views where name = @targetObjectName)
if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @targetObjectSchemaName, 'VIEW', @targetObjectName, NULL, NULL) )
and not exists( select * from sys.indexes where object_id = object_id( concat(quotename(@targetObjectSchemaName),'.',quotename(@targetObjectName))) )
begin
exec sp_dropextendedproperty
@name = N'HasIndex'
,@level0type = N'Schema', @level0name = @targetObjectSchemaName
,@level1type = N'View', @level1name = @targetObjectName
print 'Extended property HasIndex on view dropped.'
end
end
else if @eventType = 'CREATE_INDEX' and @targetObjectType = 'VIEW'
begin
--print @eventType
set @targetObjectSchemaName = (select schema_name(schema_id) from sys.views where name = @targetObjectName)
if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @targetObjectSchemaName, 'VIEW', @targetObjectName, NULL, NULL) )
begin
exec sp_updateextendedproperty
@name = N'HasIndex'
,@value = N'1'
,@level0type = N'Schema', @level0name = @targetObjectSchemaName
,@level1type = N'View', @level1name = @targetObjectName
print 'Extended property HasIndex on view updated.'
end
else
begin
exec sp_addextendedproperty
@name = N'HasIndex'
,@value = N'1'
,@level0type = N'Schema', @level0name = @targetObjectSchemaName
,@level1type = N'View', @level1name = @targetObjectName
print 'Extended property HasIndex on view added.'
end
end
else if @eventType = 'ALTER_VIEW'
begin
--print @eventType
if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @schemaName, 'VIEW', @objectName, NULL, NULL) where value = '1' )
begin
;throw 50001,'Cannot alter view with an index. Drop the index first.', 1;
rollback;
return;
end
end
else
begin
;throw 50001,'Unexpected event type in ddl trigger.', 1
end
end