SQL Server scalar User-Defined Functions (UDFs) have a performance problem that could be solved with an enhancement to the SQL Server database engine. Because SQL Server must execute each function on every row, using any function incurs a cursor like performance penalty. However, since many UDFs are simple, they can often be converted to a single expression. This suggestion proposes that a new type of UDF, the Scalar Expression UDF, be added to SQL Server. Doing so would often eliminate the performance penalty paid when using scalar UDFs and allow them to be used more widely. Using UDFs has the advantage of abstraction that makes code easier to maintain and so making them easier to use has substantial benefits.
No matter how simple a UDF there’s a large performance penalty paid when they’re used. This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows. If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It’s the row by row processing that seems to slow SQL Server the most. I’ve documented the performance penalty in chapter 11 of my book, Transact-SQL User-Defined Functions, and in the accompanying script. I've posted a PDF with the relevent section of the book and a complete example script on my site at: