Using user-defined functions

In writing SQL applications, you can implement some actions or operations as a user-defined function (UDF) or as a subroutine in your application. Although it might appear easier to implement new operations as subroutines, you might want to consider the advantages of using a UDF instead.

For example, if the new operation is something that other users or programs can take advantage of, a UDF can help to reuse it. In addition, the function can be called directly in SQL wherever an expression can be used. The database takes care of many data type promotions of the function arguments automatically. For example, with DECIMAL to DOUBLE, the database allows your function to be applied to different, but compatible data types.

In certain cases, calling the UDF directly from the database engine instead of from your application can have a considerable performance advantage. You will notice this advantage in cases where the function may be used in the qualification of data for further processing. These cases occur when the function is used in row selection processing.

Consider a simple scenario where you want to process some data. You can meet some selection criteria which can be expressed as a function SELECTION_CRITERIA(). Your application can issue the following select statement:

     SELECT A, B, C FROM T

When it receives each row, it runs the program's SELECTION_CRITERIA function against the data to decide if it is interested in processing the data further. Here, every row of table T must be passed back to the application. But, if SELECTION_CRITERIA() is implemented as a UDF, your application can issue the following statement:

     SELECT C FROM T WHERE SELECTION_CRITERIA(A,B)=1

In this case, only the rows and one column of interest are passed across the interface between the application and the database.

Another case where a UDF can offer a performance benefit is when you deal with large objects (LOBs). Suppose that you have a function that extracts some information from a value of a LOB. You can perform this extraction right on the database server and pass only the extracted value back to the application. This is more efficient than passing the entire LOB value back to the application and then performing the extraction. The performance value of packaging this function as a UDF can be enormous, depending on the particular situation.