[dba-SQLServer] Scalar UDFs -- NOT!

When scalar UDFs first appeared, I was a big fan. No longer. In my current
project, we had a bunch of them and in some sprocs they were called
frequently. In one sproc various UDFs were called 57 times (they were
emebedded in CASE WHEN blocks and such, mostly, but some were called from
WHERE clauses. The first sproc I investigate was taking 28 seconds to
execute. I copied it and replaced all the UDFs with hard-coded calls. The
execution time went down to 3 seconds. I then tackled another time-consuming
sproc (23 seconds) and did the same thing. The results were even more
spectacular. Execution time as reported in Query Analyzer was zero seconds.

I'm big enough to eat my own words. I've written various articles about how
cool scalar UDFs are. I take it all back.

Arthur