Check if index exists before attempting to create it

[infused-kim]

Currently if you use create_nonclustered_index() on an incremental table, the refresh fails, because the index already exists.

I understand the current pattern is to use a pre-hook with drop_all_indexes_on_table(), but that leads to all indexes having to be re-built on every refresh and that the indexes can't be used for the refresh merge.

Is there anything that prevents us from checking if an index exists in create_nonclustered_index() and skipping the creation if it does?

I have implemented it like this and so far it seems to be working well...

{% macro ii_create_clustered_index(columns, unique=False) -%}
    {{ log("Creating clustered index...") }}
    {% set idx_name = this.table + '__clustered_index_on_' + columns|join('_') %}

    if not exists(select * from sys.indexes 
                  where 
                    name = '{{ idx_name }}' and 
                    object_id = OBJECT_ID('{{ this }}')
    )
    begin

        create
        {% if unique -%}
        unique
        {% endif %}
        clustered index
            {{ idx_name }}
            on {{ this }} ({{ '[' + columns|join("], [") + ']' }})
    end
{%- endmacro %}


{% macro ii_create_nonclustered_index(columns, includes=False) %}
    {{ log("Creating nonclustered index...") }}
    {% set idx_name = this.table + '__index_on_' + columns|join('_') %}

    if not exists(select * from sys.indexes 
                  where 
                    name = '{{ idx_name }}' and 
                    object_id = OBJECT_ID('{{ this }}')
    )
    begin
        create nonclustered index {{ idx_name }}
            on {{ this }} ({{ '[' + columns|join("], [") + ']' }})
            {% if includes -%}
                include ({{ '[' + includes|join("], [") + ']' }})
            {% endif %}
    end
{% endmacro %}

[alittlesliceoftom]

Am I right that this hasn't been implemented in :

https://github.com/dbt-msft/dbt-sqlserver/blob/master/dbt/include/sqlserver/macros/indexes.sql

I'm about to test it out, I can raise as a PR if works well for me and useful ?

[dataders]

closed by #116, but related to #163