Deconstructing table-valued functions

A User-Defined Function (UDF) is like a stored procedure in that it is a block of T-SQL statements saved as an object, but it differs in that it does not generate a result set, it returns a value of a specified type. A scalar UDF is a function that returns a single value, a Table-Valued Function (TVF) is a function that returns a table.

There are two types of TVFs in SQL Server:

• Multi-statement TVFs (MSTVFs): MSTVFs declare a return table type, populates the table, then returns the table at the end of the function
• Inline TVFs: You can think of an inline TVF like a view that takes a parameter, the body of the function is a single query and the return value is the result of that query

The following is an example of a MSTVF that we can create in the AdventureWorks sample database:

CREATE OR ALTER FUNCTION dbo.ufn_FindReports (@InEmpID int)...