SQL Server user-defined functions are routines that accept parameters, perform an action, and return the result of actions as a value. This return value can be either a single scalar value or a result set.
SQL Server guru John Papa says, “A user-defined function (UDF) is a prepared code segment that can accept parameters, process some logic, and then return some data.”
The advantages of using user-defined functions in SQL Server include:
1. UDFs allow modular programming — You just create the function one time, store it in the database, and it can be called any number of times in your program. Furthermore, UDFs can be revised independently of the program source code.
2. UDFs permit faster execution — Like stored procedures, T-SQL user-defined functions minimize the compilation cost of T-SQL code by caching plans and reusing them for multiple executions. Eliminating the requirement for reparsing and reoptimization with each use means significantly faster execution times. Microsoft SQL Server TechNet points out that CLR functions offer a major performance advantage compared to Transact-SQL functions for computational tasks, string manipulation and business logic, but T-SQL functions are better suited for data-access intensive logic.
3. UDFs help minimize network traffic — UDFs reduce network traffic because an operation that filters data based on some complex constraint can be expressed as a function instead of a single scalar expression. This means the function can then invoked in the WHERE clause to decrease the number or rows forwarded to the client.
A user-defined aggregate function is an aggregate function created against a SQL Server assembly where implementation is defined by an assembly created in the .NET Frameworks common language runtime.