sql - When would you use a table-valued function? -

i'm learning functions in sql server , don't understand why/when use inline table valued function.

i've tried reading , examples still unclear me. can explain or provide easy understand use-case scenario?

table-valued functions "just" parameterized views. makes them extremely powerful encapsulating logic otherwise hidden behind opaque stored procedure. here's example:

inline table-valued function:

create function dbo.getclients (     @clientname nvarchar(50) = null ) returns table return (     select *     dbo.clients     ((a.clientname = @clientname) or a.clientname null) ); 

stored procedure:

create procedure dbo.usp_getclients (     @clientname nvarchar(50) = null ) begin;     select *     dbo.clients     ((a.clientname = @clientname) or a.clientname null) end; 

unlike stored procedure call, table-valued function allows me compose logic dbo.getclients other objects:

select * dbo.getclients(n'acme') join ... b     on a.clientid = b.clientid 

in such situations cannot imagine using stored procedure because of how restrictive when compared table-valued function. forced marshal data around myself using temp table, table variable, or application layer in order combine results multiple objects.

inline table-valued functions awesome because of "inline" bit best explained here. allows optimizer treat such functions no differently objects encapsulate, resulting in near optimal performance plans (assuming indexes , statistics ideal).