Different Types of SQL Server Functions

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). For more about stored procedure and function refer the article

Types of Function
A.System Defined Function
These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

1.Scalar Function
Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.

2.Aggregate Function
Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.

B.User Defined Function
These functions are created by user in system database or in user defined database. We three types of user defined functions.
1.Scalar Function
User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

2.Inline Table-Valued Function
User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

3.Multi-Statement Table-Valued Function
User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.