What is User Defined Functions? | What kind of User-Defined Functions can be created?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more
parameters and return a single scalar data value or a table data type.
Different Kinds of User-Defined Functions created are:
1. Scalar User-Defined Function A Scalar user-defined function returns one of the
scalar data types. Text, image and timestamp data types are not supported. These
are the type of user-defined functions that most developers are used to in other
programming languages. You pass in 0 to many parameters and you get a return
2. Inline Table-Value User-Defined Function An Inline Table-Value user-defined
function returns a table data type and is an exceptional alternative to a view as the
user-defined function can pass parameters into a T-SQL select command and in
essence provide us with a parameterized, non-updateable view of the underlying
3. Multi-statement Table-Value User-Defined Function A Multi-Statement TableValue user-defined function returns a table and is also an exceptional alternative to
a view as the function can support multiple T-SQL statements to build the final
result where the view is limited to a single SELECT statement. Also, the ability to
pass parameters into a TSQL select command or a group of them gives us the
capability to in essence create a parameterized, non-updateable view of the data in
the underlying tables. Within the create function command you must define the
table structure that is being returned. After creating this type of user-defined
function, It can be used in the FROM clause of a T-SQL command unlike the behavior
found when using a stored procedure which can also return record sets.