Brent Ozar covers a Microsoft paper which looks exciting:
Froid replaces the scalar UDF operator in the calling query with the newly constructed relational expression as a scalar sub-query.
That one statement comes with a lot of interesting gotchas that they discuss throughout the paper:
-
You won’t see the scalar function in the plan (just like we don’t currently see single-statement inline table-valued functions by name in the plan – we just see their effects, kinda like views)
-
The function and the underlying tables might have different permissions (you could have permissions on the function but not the tables, or vice versa, which makes compilation & execution a little trickier)
-
Code that doesn’t get used can just get removed outright (just like SQL Server can do join elimination)
-
Costs and row estimates are now useful inside the plan
To the extent that this works (and I hope it does), it helps fulfill the promise of SQL Server 2000, with encapsulation of code. Today, one of the easiest big performance gains I can give is to strip something out of a user-defined function and inline it.