Categories
sql sql-server tsql

When would you use a table-valued function?

I’m currently learning about functions in sql server and I don’t understand why/when you would use an inline table valued function.

I’ve tried reading about it and some examples but it is still unclear to me. Can someone explain or provide an easy to understand use-case scenario?

Table-valued functions are “just” parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here’s an example:

Inline Table-valued Function:

create function dbo.GetClients (
@clientName nvarchar(max) = null
)
returns table
return (
select *
from dbo.Clients as a
where ((a.ClientName = @clientName) or a.ClientName is null)
);

Stored Procedure:

create procedure dbo.usp_GetClients (
@clientName nvarchar(max) = null
)
as
begin;
select *
from dbo.Clients as a
where ((a.ClientName = @clientName) or a.ClientName is null)
end;

Unlike the stored procedure call, a table-valued function allows me to compose the logic from dbo.GetClients with other objects:

select *
from dbo.GetClients(N'ACME') as a
join ... as b
on a.ClientId = b.ClientId

In such situations I cannot imagine using a stored procedure because of how restrictive it is when compared to the table-valued function. I would be forced to marshal the data around myself using a temp table, table variable, or application layer in order to combine results from multiple objects.

Inline table-valued functions are especially awesome because of the “inline” bit which is probably best explained here. This allows the optimizer to treat such functions no differently than the objects they encapsulate, resulting in near optimal performance plans (assuming that your indexes and statistics are ideal).