No Column Store Indexes With In-Memory Tempdb? No Problem!

Old Habits

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 body server.

You may even do crazy things like this.

Stone Cold

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.

Yonder Problem

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.

The good news is that this works with *real* tables, too.

And you can get plans with all sorts of Batchy goodness in them.

Long way from home

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!

Leave a Reply

Your email address will not be published. Required fields are marked *