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.