SQL Server schema upgrade fails when creating a filtered index based on a new column

[MichaelJLiu]

To perform a schema upgrade that simultaneously adds a new [Field] and a new [Index] whose Filter references the new field, DataObjects.Net generates a single SQL batch that contains the following:

• An ALTER TABLE statement for the new column
• A CREATE INDEX statement for the new index

For some reason, however, SQL Server does not allow the WHERE clause of a CREATE INDEX statement to reference a column that is added in the same batch.

-- Repro for SQL Server 2019:
CREATE TABLE T (A int);
GO
ALTER TABLE T ADD X int, Y int, Z int;
CREATE INDEX IX_T ON T (X) INCLUDE (Y) WHERE (Z IS NOT NULL); -- Invalid column name 'Z'.

Therefore, the schema upgrade fails:

Xtensive.Orm.SyntaxErrorException: SQL error occurred.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Z'.

Suggested resolution: Execute the ALTER TABLE and CREATE INDEX statements in separate batches.