Types of Functions in SQL Server 2008 R2

There are 3 types of functions in SQL Server 2008 R2 :

"Table Valued Function"

Understanding Inline Table Valued Function (ITVF)

   Link - "Understanding ITVF with example

Understanding Multistatement Table Valued Function (MSTVF)
   Link - "Understanding MSTVF with example"

"Scalar Valued Function" 


Understanding Multistatement Scalar Valued Function (MSTVF)
   Link - "Understanding MSSVF with example"


Understanding Inline Scalar Valued Function (ISVF)
   Link - "Understanding ISVF with example"


"System Function"

     Return a single data value of the type defined in the  RETURNS clause

     For an inline scalar function, there is no function body; the scalar value is the result of a single statement. 
     For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Inline user-defined functions follow these rules:

• The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
• There is no function_body delimited by BEGIN and END.
• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
• The table-valued function accepts only constants or @local_variable arguments