13 November 2009

Do Scalar UDFs give SQL Server a Bad Name?

Many developers seem to regard SQL Server as if it were a science-fiction alien planet where unsuspecting crew-members in blue jumpers occasionally die horribly; everything is suddenly unsafe, and potentially malicious: nothing really works properly and so any serious code should be kept well away from it. Is this developer ignorance, or is their fear justified?

The answer is, of course, somewhere in between; but SQL Server certainly does itself no favors by tolerating, and avoiding, features with ‘known problems’, rather than fixing them. It continues to ignore issues such as the performance of scalar UDFs. The problem is essentially that the optimizer deals with them very inefficiently. It generally can’t decompose and rearrange the function as if it were inline, so the function gets executed once for every single row returned. This often has a huge performance impact.

Very recently, I had a conversation with a developer who had encountered exactly this problem during a commercial development project. It was a very familiar tale. As he said it to me, he was just a .NET guy, not a “SQL Server expert”, but it was clear that he was simply astonished that something as fundamental as encapsulating reusable logic in a function could cause such drastic performance problems in SQL Server.

The problem has been known ever since UDFs were introduced but it has never been resolved, despite an apparently reasonable and supported Connect request. The reason for Microsoft dragging its feet on this issue appears to be that the workaround is relatively straightforward. It sometimes takes a bit of thinking and ingenuity but, in most cases, a developer can turn a scalar UDF into an inline UDF, as documented by Itzik Ben-Gan, among others.

Seasoned Database Developers just know about the problem and avoid it. One can blame developers that fall foul of the dangers of UDFs for not making the effort to understand how SQL Server works before using it, but on the other hand, if SQL Server cannot make even these basic concessions to universally-accepted ideas of sound programming practices, then maybe it can’t complain about the feeling of apprehension amongst developers who use it only occasionally. UDFs are not the only flawed feature that adds to the perception that SQL Server is quirky. For years, error handling was another, lack of good documentation tools, and so on.

It isn’t even a good idea for seasoned Database Developers to just know about the problem and avoid it. Problems such as UDFs and SELECT..INTO get mythologized, and DBAs start to flinch from even the most harmless usage of flawed parts of the language and avoid features long after they are fixed. Given how persistent such myths can be, even if Microsoft fixed scalar UDFs tomorrow it could still be another 10 years before the “functions don’t work in SQL Server” myth worked its way out of the system, and developers and DBAs were convinced that they were safe to use again.



Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 4212 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Tony Davis

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page.

As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management.

In his spare time, he enjoys running, football, contemporary fiction and real ale.

View all articles by Tony Davis