User-Defined Function Recommendations

This section contains recommendations and tips for working with user-defined functions, including information about scalar and table-valued functions, the effects that changes to the schema can have on functions, and the use of nested functions to simplify complex functions.

Where Scalar Functions Are Useful

Scalar functions are useful in places where you need to do the same mathematical calculations at multiple places in code. For example, if calculating interest based on percent rate, principal, and years is done throughout your application, it can be coded as a callable function, as follows.

create function calc_interest ( @principal int , @rate numeric(10,5) , @years int )
returns int
as
begin
    declare @interest int
    set @interest = @principal * @rate * @years / 100
    RETURN(@interest)
end

Using System Functions as Building Blocks

System functions can be used as building blocks for a user-defined function. For example, if you need to calculate the quadrupled value of a number, use the SQUARE system function to arrive at the value instead of writing the entire function from scratch.

Nesting Functions to Divide and Simplify a Complex Function

Nesting of functions is allowed; therefore, it might be better to break down a complex function into simpler functions and use the simpler functions together to produce the result. The advantage of breaking complex functions into smaller functions is that this code can be reused in more places in the application.

For example, suppose you need to calculate the area of a plot of land and the input can be in either meters or feet, but the area must always be displayed in square feet. Instead of writing one function that does all the work, you can break up the task into two functions:

• cnvt_meters_feet does the conversion from meters to feet
  
  
• calc_Area_ft calculates the area in feet

This way, you can use the cnvt_meters_feet function at other places in the code.

USE pubs
GO
CREATE FUNCTION cnvt_meters_feet ( @value numeric(10,3) )
RETURNS numeric(10,3)
AS
BEGIN
    DECLARE @ret_feet numeric(10,3)
    SET @ret_feet = @value * 3.281 ---1 Meter=3.281 Feet
    RETURN(@ret_feet)
END
GO
CREATE FUNCTION calc_area_ft ( @length numeric(10,3), @width numeric(10,3), @Unit char(2) )
RETURNS numeric(10,3)
AS
BEGIN
    DECLARE @area numeric(10,3)
    ---Check for unit, if meters(MT), convert it to feet(FT)
    IF @Unit = 'MT'
    BEGIN
        SET @length = pubs.dbo.cnvt_meters_feet( @length )
        SET @width = pubs.dbo.cnvt_meters_feet (@width )
    END
    ---Calculate Area
    SET @area = @length * @width
    RETURN ( @area )
END
GO
SELECT pubs.dbo.calc_area_ft ( 100.0, 50.0, 'MT') AS 'Area in Feet'
SELECT pubs.dbo.calc_area_ft ( 100.0, 50.0, 'FT') AS 'Area in Feet'
go

Avoiding the Default of Returning All Rows

When using the input parameter of the function as a condition in a WHERE clause, the number of rows returned should be considered for all possible values.

For example, if you are using the condition "WHERE name like '@value%' " as the only condition and you are relying on the user to specify the starting value, but the user does not specify any value, the WHERE condition transforms to "WHERE name like '%' ", which will return ALL the rows in the table. This will be detrimental on a multi-million-row table. To avoid this excessive result set, you can implement a default checking mechanism so that when no input is specified, only a portion of the rows is returned.

Consider Effects of Changes to the Schema

If "SELECT * FROM <table>" is being used in a function, effects of changes to the schema after creation of the function should be considered. If the function is not created with the SCHEMA_BINDING option, changes to the schema are not reflected in the result.

For example, if a new column is added to the table after the function was created and the function is not SCHEMA bound, the new column will not show up in the result set. If a column is removed after creation of the function and the function is NOT SCHEMA bound, a NULL value will show up in the result set for the deleted column.

Using Subsets to Consolidate Stored Procedures and User-Defined Functions

Table-valued functions can be defined to return a wide result set. Different users can then use the subsets of the result to retrieve the data accordingly. This can be used to consolidate multiple stored procedures or user-defined functions.

For example, you can create the functions as follows:

• FunctionA returns Col1, Col2, Col3, ... Col10 from TableA
  
  
• FunctionB returns Col1, Col3 from FunctionA.
  
  
• FunctionC returns Col2, Col4 from FunctionA.

Now different users can retrieve smaller subsets by using FunctionB or FunctionC. They can also select the subset of the columns returned by FunctionA by using a simple SELECT statement. Example: Select Col10 from FunctionA.

Eliminating Temporary Table Usage

Multi-statement table-valued functions can be used to eliminate temporary table usage for intermediate result processing.

When to Convert Stored Procedures into Table-Valued Functions

Evaluate the reasons for conversion; do not convert stored procedures into table-valued functions just for uniformity. Even though some improvements are expected, test the conversion thoroughly to confirm the expectations and check for any unwanted side effects.