User Defined Function Performance...

Simon blogs about the difference in performance between using T-SQL functions directly, placing the code in T-SQL functions, and using CLR functions. He discovers that the overhead of functions is non-trivial.

Now, if moving functionality out to functions (T-SQL or CLR) makes your system easier to understand, then for all but the most intense systems, I would still recommend using functions, as breaking things into functions generally makes your code much more understandable. Note that the CLR version of the function worked significantly quicker than the T-SQL function, but slower than moving the code in-line. That said, if a function is used in a dozen queries, having the code in the function will save a whole lot of developer time if there needs to be a change in the code.

Posted by Douglas Reilly at 09:24 AM  Permalink