UDF represents user-DEFINED represents which are designed to accept the parameter and does processing on parameter before returning a result set of processing or actions. The result could be either a scalar value or a complete result set. The UDF’s are widely used in scripts, stored procedures and within other’s UDF as well.        

There are several benefits of using UDF :

1. UDF is designed keeping in mind modular programming. Once you have created UDF, we can call the UDF multiple times and it is not dependent on source code so we can easily MODIFY it.
2. UDF is designed to reduce compilation cost by caching plan so we can REUSE them without compiling it.
3. The WHERE clause is considered as an expensive operation so if we have some filter on complex constraints we can easily create UDF for the same and same UDF can be replaced in WHERE clause.

There are two types of UDF based on the output value of UDF’s

• The scalar functions-The output of this function is scalar VALUES. It may accept zero or more parameters and can return any SQL defined data type other than text, ntext, image, cursor, and timestamp.

• Table values functions- This type of function return table as resultset. It can accept zero or more parameters but always return table.