Liquibase Code generated for a conditional index gives error in MS SQL Server

Question

I have this following index in my SQL Server Database

CREATE NONCLUSTERED INDEX XCR_ACTIVE ON dbo.CS_PA_VEH_BASE ( ETL_ACTIVE_FL )
WHERE ETL_ACTIVE_FL = 'N' ON "default";

for which liquibase gives out following:

<createIndex indexName="XCR_ACTIVE" tableName="CS_PA_VEH_DELTA">
    <column computed="true" name="([ETL_ACTIVE_FL]='N')"/>
</createIndex>

But when I want to execute it (also in MS SQL Server) I get the following

> **Error:**  (14.2) 05-24-19 11:43:05 (E) (13004:15088) RUN-050304: |Session JOB_ODS_Liquibase Function call <raise_exception ( Liquibase
> update error: 1: Unexpected error running Liquibase: Incorrect syntax
> near '('. [Failed SQL: CREATE NONCLUSTERED INDEX XCR_ACTIVE ON
> [dbo].[CS_PA_VEH_DELTA](([ETL_ACTIVE_FL]='N'))] ) > failed, due to
> error <50316>: <Liquibase update error:-1: Unexpected error running
> Liquibase: Incorrect syntax near '('. [Failed SQL: CREATE NONCLUSTERED
> INDEX XCR_ACTIVE ON [dbo].[CS_PA_VEH_DELTA](([ETL_ACTIVE_FL]='N'))]>.

I hope it's not the first time this is happening to someone.

Answer 1

Nothing is mentioned about "Indexes with Filter" in the standard Liquibase documentation: https://www.liquibase.org/documentation/changes/create_index.html but what Nathan mentions here http://forum.liquibase.org/topic/how-to-create-conditional-indexes-using-createindex?reply=true about modifySQL should work.

Answer 2

As you mentioned in your answer, there is no support for partial/conditional/filtered indexes in Liquibase.

What you tried to do, is to use computed attribute to set a condition for filter. Documentation for computed states:

The attribute to set to true if the value in name isn't actually a column name but a function. Boolean.

So, it is only used if you want to programmatically name the column to be indexed. So you either have to use pure SQL or modifySql.

---

modifySql

With modifySql you can append sql before or after generated statements, or replace parts of it. If we try with append it would look like this:

<changeSet id="create-an-index-modifySql" author="potato">
    <createIndex tableName="CS_PA_VEH_BASE"
                 indexName="XCR_ACTIVE"
                 clustered="false">
       <column name=ETL_ACTIVE_FL>
    </createIndex>
    <modifySql>
        <append value="WHERE ETL_ACTIVE_FL = 'N'">
    </modifySql>
</changeSet>

Which will produce following sql statement:

CREATE NONCLUSTERED INDEX XCR_ACTIVE ON CS_PA_VEH_BASE ( ETL_ACTIVE_FL )
WHERE ETL_ACTIVE_FL = 'N';

The issue with this approach is when using tablespace attribute; WHERE will be appended after ON tablespace. Example:

<changeSet id="create-an-index-modifySql" author="potato">
    <createIndex tableName="CS_PA_VEH_BASE"
                 indexName="XCR_ACTIVE"
                 tablespace="default"
                 clustered="false">
       <column name=ETL_ACTIVE_FL>
    </createIndex>
    <modifySql>
        <append value="WHERE ETL_ACTIVE_FL = 'N'">
    </modifySql>
</changeSet>

CREATE NONCLUSTERED INDEX XCR_ACTIVE ON CS_PA_VEH_BASE ( ETL_ACTIVE_FL )
ON default WHERE ETL_ACTIVE_FL = 'N';

which has wrong syntax.

The solution would be to use modifySql with replace and with attributes:

<changeSet id="create-an-index-modifySql" author="potato">
    <createIndex tableName="CS_PA_VEH_BASE"
                 indexName="XCR_ACTIVE"
                 tablespace="default"
                 clustered="false">
       <column name=ETL_ACTIVE_FL>
    </createIndex>
    <modifySql>
        <append replace="ON default" with="WHERE ETL_ACTIVE_FL = 'N' ON default">
    </modifySql>
</changeSet>

CREATE NONCLUSTERED INDEX XCR_ACTIVE ON CS_PA_VEH_BASE ( ETL_ACTIVE_FL )
WHERE ETL_ACTIVE_FL = 'N' ON default;

Although correct, I consider it too fiddly and error prone.

---

Pure SQL

The safest bet would be to create a changeSet with sql tag, something like this:

<changeSet id="create-an-index-pure-sql" author="potato">    
    <sql>
        CREATE NONCLUSTERED INDEX XCR_ACTIVE
            ON dbo.CS_PA_VEH_BASE (ETL_ACTIVE_FL)
        WHERE ETL_ACTIVE_FL = 'N' ON "default";
    </sql>
</changeSet>

Although it is not in the spirit of Liquibase, I consider it good enough solution for filtered indexes.