Table Valued Function (TVF) in T-SQL (Transact-SQL) returns a table. Especially this feature is useful with Entity Framework because EF exposes this table class, whereas the Stored Procedure requires a manual extension to the database context by creating an additional class that Stored Procedure returns so that your .NET code can consume it when you call the Stored Procedure.
There are two types of TVF.
1. Inline Table-Valued Function
It returns a table from one SELECT block. You cannot insert any intermediate logic before returning a table.
2. Multi-statement Table-Valued Function
You can insert intermediate logic before returning a table. This is very similar to Stored Procedure.
INLINE TABLE-VALUED FUNCTION EXPLAINED
The example above describes an Inline TVF. As you see there is no intermediate logic and you are immediately returning a table using a SELECT code block. This is useful when you have special JOINs, WHEREs, and / or SELECTs only certain columns, that you can write in ONE SELECT clause.
MULTI-STATEMENT TABLE-VALUED FUNCTION EXPLAINED
The "crude" and "simple" example above describes a case where you add intermediate logic to manipulate data BEFORE returning the table. It will add one type of record "staff" and then another type "customer" to the table. Then the returned table will have both types in one table. This is an example of Multi-Statement TVF (you see it is similar to a stored procedure). In addition to intermediate logic, another difference is that you must define a table structure in the beginning of the function. As you can tell, this can also be written in Inline TVF using UNION.
Comments