Missed It The First Time
This is documented, but I know a lot of people are allergic to documentation, whether it’s writing or reading.
In SQL Server 2019:
- Exciting stuff: In memory tempdb!
- Exciting stuff: sp_estimate_data_compression_savings can evaluate columnstore compression!
- Disappointing stuff: If you use in memory tempdb, you can’t have any columnstore anything in tempdb
That means if you’re using sneaky tricks like clustered columnstore indexes on temp tables to induce batch mode, you’re gonna get a lot of errors.
Likewise, you won’t be able to evaluate if columnstore will help your tables.
EXEC sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Badges', @index_id = 1, @partition_number = 1, @data_compression = 'COLUMNSTORE';
Msg 11442, Level 16, State 1, Line 4
Columnstore index creation is not supported in tempdb when memory-optimized metadata mode is enabled.
There’s no workaround for this, either. You can’t tell it to use a different database, this is just the way it’s built.
Hopefully in the future, there will be more cooperation between these two features.
Thanks for reading!