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 Chapter 4, Exploring Query Execution Plans, under the Query plan properties of interest section, as shown in the following query:

CREATE FUNCTION ufn_CategorizePrice (@Price money)
RETURNS NVARCHAR(50)
AS
BEGIN
       DECLARE @PriceCategory NVARCHAR(50)
       IF @Price...