SQL Server 2019: What Kind Of Functions Can’t Be Inlined?

Dating Sucks

There’s a lot of excitement (alright, maybe I’m sort of in a bubble with these things) about SQL Server 2019 being able to inline most scalar UDFs.

But there’s a sort of weird catch with them. It’s documented, but still.

If you use GETDATE in the function, it can’t be inlined.

Say What?

Let’s look at three examples.

Numero Uno

This function can’t be inlined. It uses the GETDATE function directly in a calculation.

I’m not bothered by that! After all, it’s documented.

In writing.

Numero Dos

I was thinking that maybe if we just calculated the date once in a variable and then use that, we’d be able to inline the function.

But no.

No we can’t.

Numero Tres

What if we don’t even touch GETDATE? Hm?

No.

Still no.

Kinda Weird, Right?

If you’re using SQL Server 2019 and want to find functions that can’t be inlined, start here:

None of these functions can be inlined:

Bummer.

Unfortunately, the only real solution here is to rewrite the function entirely as an inline table valued function.

Thanks for reading!

2 thoughts on “SQL Server 2019: What Kind Of Functions Can’t Be Inlined?”

  1. I think this comes down to how scalar functions are RBAR. A scalar function that just returns GETDATE() will provide different values (depending on time elapsed). On my tally table of 1M rows, I got ~275 distinct values. With a TVF version, I get a single value.

    Of course, people probably want the TVF functionality anyway.

Leave a Reply

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