sql-serverquery-optimizationuser-defined-functions

SQLServer: Why avoid Table-Valued User Defined Functions?


I have a rather huge query that is needed in several stored procedures, and I'd like to shift it into a UDF to make it easier to maintain (A view won't work, this takes in a bunch of parameters), however everyone I've ever talked to has told me that UDF's are incredibly slow.

While I don't know what exactly makes them slow, I'm will to guess that they are, but seeing as I'm not using this UDF within a join, but instead to return a table variable, I think it wouldn't be that bad.

So I guess the question is, should I avoid UDFs at all cost? Can anyone point to concrete evidence stating that they are slower?


Solution

  • Scalar UDFs are very slow, inline UDFs are in fact macros, as such they are very fast: A few articles:

    Reuse Your Code with Table-Valued UDFs

    Many nested inline UDFs are very fast

    More links on slowness of scalar UDFs:

    SQL Server Performance patterns of a UDF with datetime parameters

    Not all UDFs are bad for performance