• I also think it's worth mentioning that Scalar UDFs, although certainly can be bad especially when compared to their iTVF cousins, are usually not the top concern on a system with performance problems.  It's more likely that things like accidental many-to-many joins that the programmer tried to overcome with the likes of DISTINCT, are usually much worse than any mere Scalar UDF.  Things like an index seek occurring thousands of times in the same query (1 seek per row) are forms of "hidden RBAR".  Things like datatype mismatches on join criteria and non-SARGable queries can also cause much more of a problem than even a shedload of Scalar UDFs.  Then, there's ORM code which may be recompiling every time it runs (a problem that's difficult to find for most and can be much worse.... We just fixed such a thing in our system that was taking 2-17 seconds to compile and "only" took 100ms to execute that runs thousands of times per hour).

    The bottom line is that there are a ton of things much worse than Scalar UDFs.  You could end up finding and replacing every Scalar UDF and still have a system in deep kimchee.  Don't waste your time on a "pre-optimization" effort of finding and replacing all Scalar and Multi-Statement Table Valued Functions (mTVFs) with iTVFs (inline Table Valued Functions) unless they are actually in the top ten of your actual performance problems.  Instead, fix those top 10 problems.  "Keep your eye upon the donut and not upon the hole". 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)