How Scalar Functions Can Stop You From Getting Adaptive Joins

If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days.

If you are, and you’re worried about them crapping on Adaptive Joins, follow along.

The big question I had is if various uses of scalar valued functions would inhibit Adaptive Joins, and it turns out they’re a lot like non-SARGable queries.

Starting with a simple function that doesn’t touch anything.

We can all agree that it doesn’t access any data and just returns the INT max. Now some queries that call it!

If you’re the kind of monster who puts scalar functions in WHERE clauses, you deserve whatever you get. That’s like squatting in high heels.

Not that I’ve ever squatted in high heels.

Alright look, what’s that Brent says? I was young and I needed the money?

Let’s forget about last week.

Query Plans!

By this point, you’ve seen enough pictures of Adaptive Join plans. I’ll skip right to the plan that doesn’t use one.


It’s for the last query we ran, with the scalar function in the WHERE clause with a predicate on the Posts table.

See, this isn’t SARGable either (and no, SCHEMABINDING doesn’t change this). When a predicate isn’t SARGable, you take away an index seek as an access choice. You don’t see too many Nested Loops with an index scan on the other end, do you?


So there you go. It’s not the function itself that bops our Adaptive Join on the head, but the lack of SARGability.

Thanks for reading!

Previous Post
SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions
Next Post
Building a Faux PaaS, Part 2: Choosing and Testing a Cloud Vendor

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.