Inline function versus normal select

Question

Now I am studying about functions in SQL Server 2012. I knew that there are three function types. But I am confused by Inline Table-Value Functions.

Let me explain.

select * into Number 
from (
 values 
  (1, 'A'),(2, 'B'),(3, 'C')
) number (N, C)

create function InlineFun (@i int)
returns table
as
return (select * from Number where n = @i)

select * from Number where n = 1
select * from InlineFun (1)

From the above select's the results are the same. Then what is the exact scope of Inline TVF..?

Note :

I am using Microsoft SQL Server 2012

score 1 · Accepted Answer · answered Dec 19 '18 at 16:22

1

Think of an ITVF as a view you can pass parameters to. It is essentially included in your script that references it as if it was plain old SQL and then executed. This is why they perform better than multi statement table valued functions, which have to be executed as a seperate statement.

Due to this, in your examples the statement:

select *
from InlineFun (1)

Is essentially passed to the query engine as:

select *
from (select *
      from Number
      where n = 1
     ) as a

So to actually answer your question, the scope of the function is the same as the scope of the statement that calls it.

answered Dec 19 '18 at 16:22

iamdave

12,023
3
24
53

so, there is no daze scope for `ITVF`. – Pugal Dec 19 '18 at 16:32
I am not sure what you mean by "daze scope" – iamdave Dec 19 '18 at 16:33
Sorry @iamdave Just I tried a phrase using Google. What I meant that __there is no special scope for ITVF__ – Pugal Dec 20 '18 at 08:09
@IdontKnowEnglish That is correct. If my answer was what you were looking for, please mark it as such for other users with the same question in the future – iamdave Dec 20 '18 at 09:50
I will ask more about `SQL Function`. Keep supporting. – Pugal Dec 20 '18 at 11:07

Inline function versus normal select

1 Answers1