Avoiding UDF pitfalls

Scalar User-Defined Functions (UDFs) are a very useful T-SQL programming artifact because they allow a specific routine to be reused very easily. However, these seemingly harmless constructs can be detrimental to performance, because the Query Optimizer does not account for any T-SQL logic inside a UDF, and UDFs are executed for every row in the result set just like a cursor. When using scalar UDFs, there are specific recommendations that apply to UDFs that access system or user data, and recommendations that apply to all UDFs.

An example of a scalar UDF that does not access data was referenced in the chapter Exploring Query Execution Plans, in the Query plan properties of interest section, as seen in the following code block:

CREATE FUNCTION ufn_CategorizePrice (@Price money)
RETURNS NVARCHAR(50)
AS
BEGIN
     DECLARE @PriceCategory NVARCHAR(50)
     IF @Price < 100 SELECT @PriceCategory = 'Cheap...