Like programming languages SQL Server also provides User Defined Functions (UDFs). From SQL Server 2000 the UDF feature was added. UDF is a programming construct that accepts parameters, does actions and returns the result of that action. The result either is a scalar value or result set. UDFs can be used in scripts, Stored Procedures, triggers and other UDFs within a database.

Benefits of UDF

1. UDFs support modular programming. Once you create a UDF and store it in a database then you can call it any number of times. You can modify the UDF independent of the source code.
2. UDFs reduce the compilation cost of T-SQL code by caching plans and reusing them for repeated execution.
3. They can reduce network traffic. If you want to filter data based on some complex constraints then that can be expressed as a UDF. Then you can use this UDF in a WHERE clause to filter data.

Types of UDF

1. Scalar Valued Functions.
2. Table Valued Functions.