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.
To perform a schema upgrade that simultaneously adds a new
[Field]
and a new[Index]
whoseFilter
references the new field, DataObjects.Net generates a single SQL batch that contains the following:ALTER TABLE
statement for the new columnCREATE INDEX
statement for the new indexFor some reason, however, SQL Server does not allow the
WHERE
clause of aCREATE INDEX
statement to reference a column that is added in the same batch.Therefore, the schema upgrade fails:
Suggested resolution: Execute the
ALTER TABLE
andCREATE INDEX
statements in separate batches.