SQL | User-Defined Functions
User-defined functions (UDFs) are routines that accept parameters, perform complex functions, and return a value or the result set.
There are three types of UDFs – user-defined scalar functions, table-valued functions, and system functions.
We use UDFs because:
- They can be created, stored, and called at any number of times.
- They allow faster execution because UDFs don’t need to be reparsed or reoptimized.
- They minimize network traffic as it reduces the number of rows sent to the client.
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.
Scalar User Defined functions
Scalar User Defined functions are commonly used in SQL Server to simplify complex queries and to encapsulate business logic.
They can be used in queries just like any other SQL function, and their return value can be used as a column value, a part of a where clause, or in any other expression.
Pre-defined functions that are defined by the system are known as system functions.