What Are User-Defined Functions (UDFs) In SQL SERVER? What Are Its Types And Benefits?

SQL Server allows you to create your own T-SQL functions called User-defined functions. The function you write can have zero or more parameters, and can return a single value, or a table data type.

Three types of User-Defined functions exist in SQL Server 2000. These are:

1) Scalar User-defined functions – these are the most basic functions that return a scalar data type.

2) Inline Table value user defined functions - This type of function returns a table data type and can be used as an substitute to a view since the user-defined function can transfer parameters into a T-SQL select command and return a table view of the underlying tables.

3) Multi-statement Table-value User-defined functions - This type of function also returns a table, only difference being it allows compound statements within the body of the function.


BENEFITS OF USER-DEFINED FUNCTIONS

1) User-defined functions are executed as an inline query thus they are easier to use than stored procedures.

2) Scalar User-defined functions can be called from the WHERE or HAVING clause of a query. In UPDATE queries they can be called in the SET clause.

3) The table that a User-defined function returns can be treated as a row set; it can be joined to other tables through code.

4) In multi-statement functions additional logic like variable declaration, populating of table variables, looping through records can take place.

Add Comment

Report