Convert_regular_column_of_temporal_table_to_sparse failing

[ajcvickers]

This test fails consistently on my machine with:

Microsoft.Data.SqlClient.SqlException
Cannot alter table 'HistoryTable' because the table either contains sparse columns or a column set column which are incompatible with compression.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__208_1(IAsyncResult result)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Storage\RelationalCommand.cs:line 204
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Storage\RelationalCommand.cs:line 268
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Storage\RelationalCommand.cs:line 273
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Migrations\Internal\MigrationCommandExecutor.cs:line 122
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Migrations\Internal\MigrationCommandExecutor.cs:line 137
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Migrations\Internal\MigrationCommandExecutor.cs:line 142
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) in C:\github\efcore\src\EFCore.Relational\Migrations\Internal\MigrationCommandExecutor.cs:line 147
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsTestBase`1.Test(IModel sourceModel, IModel targetModel, IReadOnlyList`1 operations, Action`1 asserter, MigrationsSqlGenerationOptions migrationsSqlGenerationOptions) in C:\github\efcore\test\EFCore.Relational.Specification.Tests\Migrations\MigrationsTestBase.cs:line 2033
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlServerTest.Convert_regular_column_of_temporal_table_to_sparse() in C:\github\efcore\test\EFCore.SqlServer.FunctionalTests\Migrations\MigrationsSqlServerTest.cs:line 6733
   at Xunit.Sdk.TestInvoker`1.<>c__DisplayClass48_0.<<InvokeTestMethodAsync>b__1>d.MoveNext() in /_/src/xunit.execution/Sdk/Frameworks/Runners/TestInvoker.cs:line 276
--- End of stack trace from previous location ---
   at Xunit.Sdk.ExecutionTimer.AggregateAsync(Func`1 asyncAction) in /_/src/xunit.execution/Sdk/Frameworks/ExecutionTimer.cs:line 48
   at Xunit.Sdk.ExceptionAggregator.RunAsync(Func`1 code) in /_/src/xunit.core/Sdk/ExceptionAggregator.cs:line 90

[maumar]

https://learn.microsoft.com/en-us/sql/relational-databases/tables/changing-the-schema-of-a-system-versioned-temporal-table?view=sql-server-ver16&redirectedfrom=MSDN#important-remarks indicates that sparse columns do have an issue when interacting with temporal tables, and that's why I added the test in the first place. Looks like your SQL Server uses compression (which is supposed to be the default, looking at the link above), but I just checked and what I get on my box (and presumably on CI) is that Compression Type is set to NONE.

[maumar]

btw, same thing happens for regular tables if you set compression level to ROW or PAGE before we apply the migration. We could consider checking the compression level and removing it if needed before adding (or converting to) sparse column, but maybe that's too much "magic". Thoughts? @bricelam @roji