Let’s say you’re on SQL Server 2019. No, seriously. It’s been out for a couple weeks now.
You could be.
I say that you could be because you’re the kind of brave person who tries new things and experiments with their
You may even do crazy things like this.
CREATE TABLE #t ( id INT, INDEX c CLUSTERED COLUMNSTORE ); SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId JOIN dbo.Comments AS c ON u.Id = c.UserId LEFT JOIN #t AS t ON 1 = 0;
Woah ho ho. What happened there? A #temp table with a clustered column store index on it left joined on 1 = 0?
Yes. People do this.
People do this because it’s getting some batch mode operations “for free”, which have the nasty habit of making big reporting queries run a lot faster.
When you enable 2019’s new in memory tempdb, which can really help with stuff tempdb needs help with, you may find yourself hitting errors.
Msg 11442, Level 16, State 1, Line 14 Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled. Msg 1750, Level 16, State 1, Line 14 Could not create constraint or index. See previous errors.
The good news is that this works with *real* tables, too.
CREATE TABLE dbo.t ( id INT, INDEX c CLUSTERED COLUMNSTORE ); SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId JOIN dbo.Comments AS c ON u.Id = c.UserId LEFT JOIN dbo.t AS t ON 1 = 0;
And you can get plans with all sorts of Batchy goodness in them.
Yeah, you’re gonna have to change some code, but don’t worry.
You’re the kind of person who enjoys that.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Batch Mode On Row Store vs Batch Mode Tricks For Performance Tuning SQL Server Queries
- Making The Most Of Temp Tables Part 4: Batch Mode
- SQL Server 2019 Disappointment: sp_estimate_data_compression_savings
- Even If SQL Server Table Variables Were Always In Memory, It Wouldn’t Make Them Better Than Temp Tables