User Defined Function

User Defined Function

A user defined function takes 0 or more parameter and return a scalar or table value data type. User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.
There are three type of User Defined Function
i. Scalar Function
ii. Single Table Valued Function
iii. Multistatement Table Valued Function
Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.
Inline table-valued functions return the result set of a single SELECT statement.
Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.
Notes: On Microsoft SQL Server 2000 a table-valued function which 'wraps' a View may be much faster than the View itself. On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the 'function-wrapper'.