SQL Server 2005 compatibility issue- migration failure with filtered index queries

[pstumps]

Migration fails when running migrate on SQL Server 2005 with error code pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW)")
It seems to be a direct result of when queries that attempt to filter by index are executed similar to this: CREATE UNIQUE INDEX ... ON [table] (col1, col2, ...) WHERE col1 IS NOT NULL AND col2 IS NOT NULL

I believe this error occurs because the ability to filter by index was not introduced until SQL Server 2008. Removing the 'where' statement allows migrate to progress, however, I'm not sure what the consequences of doing so are.

Any suggestions on how to handle this would be appreciated.

[absci]

Is it possible to upgrade to a newer version of SQL Server?

[bobince]

The WHERE is a workaround for a non-ANSI-compliant behaviour in mssql, where NULL is counted as a distinct value for UNIQUE constraints. Consequently if you had a null=True, unique=True field, you could only have a single row with a NULL in that column.

This is redundant if the fields are non-nullable, or if you don't expect there to be more than one NULL. In that case you could get away with removing the WHERE, although it's possible this might confuse introspection for migrations that change the indexes or PKs in the future. (But this is already a bit broken, so I don't know how much worse that'd actually be.)

The workaround was introduced back under the previous fork of the backend IIRC, so you'd have to go quite a long way back to get a version that just works on 2005.