SQL Server 2012 – ALTER VIEW Drops Index from View

indexmaterialized-viewsql serversql-server-2012view

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)?

Best Answer

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
Related Question