The Problems with Scalar UDFs

Tony Davis wrote an excellent editorial on Microsoft’s inaction on improving performance with scalar UDFs.  The case he makes is well thought out and strongly echoes my own experience.  The article is work a read:

Do Scalar UDFs give SQL Server a Bad Name?

by Tony Davis www.simple-talk.com
http://www.simple-talk.com/community/blogs/tony_davis/archive/2009/11/13/76413.aspx

In the article he references an article by Itzik Ben-Gan in SQL Server Magazine that goes into some of the technical details of defining the problem.  More importantly, he shows an interesting twist to get around the problem.  The twist is to convert the scalar UDF to a table returning function since the query optimizer can work efficiently with this type of UDF.  Yes this is possible to do the conversion and use the function inline.  Clearly this solution is a workaround but very handy to have in the tool box when needed.

Inline Scalar Functions

by: Itzik Ben-Gan www.sqlmag.com
http://www.sqlmag.com/Articles/ArticleID/101104/101104.html?Ad=1
{This was part of SQL Server Magazine’s free content when this post was made.  It is ever goes behind the pay wall let me know and I will work up some demo code}

Three Cheer’s for Tony Davis’ fine article and our eternal thanks to Itzik for his contributions to our community.  I have met him 3 times now.  He had a direct impact on my career by setting me free to concentrate on T-SQL.  He explained that the SQL Server product had gotten so big that no one can do it justice to try and master it all.  Solid Quality Learning has specialist in just single areas of functionality such as replication, clustering, T-SQL, or BI.  This helped me since I was struggling to stay on top of being a Network Admin, software developer & architect, and SQL Specialist not to mention dabbling in Java and Oracle.  The experience has been very beneficial as I have moved on and now do more management level work.  The credit goes to Itzik for lifting the self-imposed psychological burden.