User-defined functions (UDFs) encapsulate logic for use in other queries. Views are limited to a single SELECT statement but user-defined functions can have multiple SELECT statements.
There are basically 3 categories of UDF:
- Scalar-valued function
- Returns a single, scalar value.
- Can be used as column name in queries.
- Can contain an unlimited number of statements as long as only one value is returned.
- Inline function
- Returns a variable of data type table.
- Can only contain a single SELECT statement.
- The structure of the returned value is generated from the columns that compose the SELECT statement.
- A table variable need not be explicitly declared and defined.
- Table-valued function
- Can contain any number of statements that populate the table variable to be returned.
- Useful when you need to return a set of rows.
- A table variable must be explicitly declared and defined.
- An advantages over a view is that the function can contain multiple SQL statements whereas a view is composed of only one statement.
Differences between Stored Procedures and UDFs:
- UDF can be used in SQL statements whereas SPs can’t.
- UDFs that return tables can be treated as another rowset and can be used in JOINs.
- Inline UDF's can be thought of as views that take parameters and can be used in JOINs etc.