Todd Young-359443 (10/15/2012)
I was able to work at home this weekend and found the answer. When callingat UDF that does not access any database table and is used more like a macro,
the performance hit is negligible.
The raw difference in a 20,000 row dataset was 200 milliseconds. When I removed
the overhead of the three DateAdd functions it dropped to 100 milliseconds.
We are keeping the function in production and have dropped this as a candidate
for optimization.
You are fooling yourself. There are MANY reasons why UDFs are bad (see my chapter in the SQL Server MVP Deep Dives 2 book titled Death By UDF and my SQL Rally 2012 session of the same name). Depending on how this UDF gets used it could prevent the optimizer from getting accurate statistics on the query and thus lead to a disastrously bad query plan. And did you know that UDFs also void the use of parallelism? Lots of other potential issues come with them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service