A User defined function is a transact-SQL or collection of one of more SQL statements that accepts parameters, perform an action and returns the result of that action. Action can be anything such as retrieving data from table or performing any calculations. The result can be a single scalar value or a table of multiple rows and columns.
Types of User Defined Functions (UDF)
On the basis of returned result and amount of statement used, User Defined functions are divided into following three types.
- Scalar-valued Function
- Inline Function
- Table-valued Function (Multi-statement Function)
Scalar-Valued Functions are similar to built-in functions because they return a single scalar value of any system defined data type except TEXT,NTEXT, IMAGE, CURSOR, and TIMESTAMP. A scalar-valued function cannot return value of user defined data type. A value returned by scalar-valued user defined function can be based on the specified parameters.
Inline functions are based on a single select statement and they return a table of one or multiple rows on the bases of that single select statement. Inline functions cannot contain much logic in it because we can only use a single select statement in it. Because of their nature of single select statement and returning a table, inline user defined functions are compared with Views.
Table-valued functions are also called multi-statement functions because we can use multiple statements in these functions. Multi-statement functions can perform any kind of loop or conditional processing.
Advantages and disadvantages of User Defined Functions
There are many advantages and disadvantages of user defined functions. Let’s discuss some of these.
Advantages of User Defined Functions
- User defined functions can be used in SELECT, WHERE, HAVING, FROM, ORDER BY and CASE statements so that will make User defined functions easier to use.
- Inline user defined functions can also be used with JOINS like views.
- User defined functions can be used as parameterized views because they work like view but they can return different data based on the supplied parameters.
Disadvantages of User Defined Functions
- A user defined function cannot return multiple result sets like stored procedure.
- We cannot create a user defined function without returning any value like store procedure.
- A user defined function can be called from stored procedure but a stored procedure cannot be called from a user defined function.
- We cannot create a user defined function with output parameter like store procedure.