Performance Implications of Scalar User Defined Functions (UDF)

One of the major new features of SQL Server 2000 was user defined functions (UDF). This new feature allowed procedures to encapsulate logic in a function.

For example:
In the Northwind Database 'Order Details' table the total sale can be calculated using the following:

SELECT CONVERT(Money,UnitPrice * Quantity * (1-Discount))FROM [Order Details]


It is possible to encapsulate this in a scalar UDF below:

CREATE FUNCTION dbo.Func_GetSale (@Price MONEY, @Quantity INT, @Discount FLOAT)
RETURNS MONEY AS
BEGIN
RETURN @Price * @Quantity * ( 1 - @Discount)
END


Then the above query could be simplified to:
SELECT dbo.Func_GetSale(UnitPrice, Quantity, Discount)FROM [Order Details]

Obviously this is simpler to understand and better from a maintenance point of view. But what about performance? If I ran the first query 5x on my computer it takes 63ms. If I run the query using the UDF it takes 140ms, over twice as long.

This above simple example shows the penalty of using UDF. I think that UDFs are great BUT they are not free. I use them frequently but keep them away from performance sensitive tasks. In a later post I will analyze and comment on the performance implications of table-valued UDF.