I have read that When a SELECT statement is about to return more than a few rows, it is better not to use a Scalar function within that SELECT statement. Instead it is better to create a Table-Valued UDF and use it with APPLY operator.
Can anyone explain that with example?
I am using MS SQL Server 2014.
First, it's not a matter of scalar functions vs table valued functions. It's a matter of "inline" vs "muliti-statement"... So simply converting your scalar valued function into table valued functions is most likely going to do more harm than good UNLESS you make sure the resulting table valued function is an INLINE TABLE VALUED FUNCTION (iTVF).
The reason it matters... Scalar & multi-statement functions are like "black boxes" to the optimizer, so the optimizer simply makes some general assumptions about the functions and blindly applies the function code to each row of the outer query, usually assigning a 0 cost to the function. This tends to lead to sub-optimal execution plans.
Inline table valued functions, on the other hand, are "viable" to the optimizer. This allows it to include the function code into the outer query's execution plan as if it were written directly in the outer query.
Now for the real brain bender... Because the optimizer is able to factor in the expense of the inline function, the resulting execution plan will have a higher estimated cost than the than the non-inline version. Essentially tricking you into believing that non-inline version is better... In fact, the opposite is true. The more expensive inline version is actually better as it actually represents the true cost of the query.
