I'm reading up on query inlining and I'm finding it very confusing.
On one hand, I'm being told that user-defined scalar-valued functions are terrible for performance because they (almost always) can't be inlined and that this stops the entire query that references them from being run in parallel. Similarly, I'm told that inline table-valued functions are awesome for performance because they can be inlined. On the other hand, I'm being told that I need to worry about repeated work and bad performance from both CTEs and derived tables because they get inlined.
Putting the above together, I'm confused about if inlining is a good thing or not and would like to see an explanation for why it is important for query optimisation. When is it a good thing? When is it a bad thing? How can I spot each of these in execution plans?
I think you are confusing three different cases:
a. Inline Table Valued Functions (iTVFs). They are called inline as opposed to multi-statement TVFs. Multi-statement TVFs store query results in temp tables, potentially causing TEMPDB contention. iTVFs do not and they tend to generally perform better (see this interesting article for a performance comparison).
b. CTE inlining. I am not sure, but I think this is relevant to the fact that a CTE is executed every time it is referenced. So, if you join a CTE to itself 3 times, the CTE will be executed 3 times. You are probably better off persisting the results of the CTE to a temp table in this case and reference the temp table in you query, instead.
c. Scalar UDF inlining. Scalar UDFs are notorious for having bad performance, due to a number of reasons, explained here (Namely: iterative execution, serial execution, lack of costing, interpreted execution). Microsoft tried to improve the situation by introducing scalar UDF inlining, in SQL Server 2019. With scalar UDF inlining, the body of the function is inlined in the query, thus yielding better execution plans. Alas, as you mention in your question, there is a set of requirements that must be fulfilled in order for a scalar UDF to be inlinable. As a result, not all scalar UDFs can be inlined and thus benefit from the performance gain offered by the better execution plan.