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?
A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table , you can use a table-valued function anywhere in SQL that you can use a table.
A table function, also called a table-valued function (TVF), is a user-defined function that returns a table. You can use a table function anywhere that you can use a table. Table functions behave similarly to views, but a table function can take parameters.
The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries.
Table-valued functions in Oracle are the functions, returning a collection of rows and can be queried like usual database tables by calling the function in the FROM clause of SELECT statements.
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).
This is a great question and a topic that's not discussed enough IMHO. Think of inline table valued functions as views that accept parameters. That's the short answer but let's dig a little deeper...
In SQL server you have three kinds of user-defined functions*: scalar functions (svf), multi-line table valued functions (mTVF) and inline table valued functions (iTVF). svfs return a single value, both mTVFs and iTVFs return a table. The difference between mTVFs and iTVFs is performance. In short - mTVFs are slow, iTVFs can be (and almost always are) much faster. mTVFs allow you to do things you couldn't do in a view (e.g. create temp tables, perform loops, utilize cursors...), iTVFs, again, have the same restrictions as views except for they can except parameters.
I use iTFVs for common data warehouse queries where I need a view that takes parameter and splitting/manipulating strings. A more advanced use of iTVFs which has changed my career is replacing scalar functions with iTVFs - see this article from Jeff Moden titled, "How to Make Scalar UDFs Run Faster": http://www.sqlservercentral.com/articles/T-SQL/91724/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With