User-defined functions in SQL Server

User-defined functions in SQL Server

User-Defined Functions (UDF: User Defined Functions:

Similar to stored procedures, user-defined functions are a set of ordered T-SQL statements, udfs are pre-optimized and compiled and can be debugged as a unit. The main difference between a UDF and a stored procedure is the way the result is returned. To support multiple different return values, UDF has more restrictions than stored procedures.

Two types of UDF:

1. Return the scalar value UDF

2. Return the UDF of the table

Function certainty:

Certainty means that the returned result is definite. or, if the same function is used without any changes, the returned value will not change under the same conditions.

Udfs can be deterministic or non-deterministic.

When a UDF needs to meet the requirements of certainty, the following four conditions must be met:

1. The function must be bound in the mode. This means that any object the function depends on will have a dependency record, and this object cannot be changed before the dependent function is deleted.

2. All other functions referenced by a function, whether user-defined or system-defined, must be determined.

3. Tables defined outside the function cannot be referenced (Table variables and temporary tables can be used as long as they are defined within the function scope ).

4. You cannot use extended stored procedures.

Function certainty check:

Select objectproperty (Object_id ('function name'), 'isterministic ');