use tempdb;
go

create table dbo.t1 (id int primary key, v varchar(10));
create table dbo.t2 (id int primary key, v varchar(10));
go

create function dbo.fn1
(
 @v varchar(10)
)
returns table
as
return (
 select id, v from dbo.t1 where v = @v
);
go 

create function dbo.fn2
(
 @v varchar(10)
)
returns table
as
return (
 select id, v from dbo.t2 where v = @v
);
go

create function dbo.fn3
(
 @p int,
 @v varchar(10)
)
returns table
as
return (
 select
  fn1.id, fn1.v
 from
  dbo.fn1(@v) fn1 cross apply
  (select 1) a(p)
 where
  a.p = @p

 union all

 select
  fn2.id, fn2.v
 from
  dbo.fn2(@v) fn2 cross apply
  (select 2) a(p)
 where
  a.p = @p
);
go

insert into dbo.t1 select top (100) object_id, 't1' from sys.objects;
insert into dbo.t2 select top (100) object_id, 't2' from sys.objects;
go

set statistics xml, io on;
select * from dbo.fn3(1, 't1');
select * from dbo.fn3(2, 't2');
set statistics xml, io off;
go

drop function dbo.fn1, dbo.fn2, dbo.fn3;
drop table dbo.t1, dbo.t2;
go

use tempdb;
go
create table t1(b int);
insert t1 values (1);
create table t2(b int);
insert t2 values (2);
go
-- Inline
create function dbo.uf_a()
returns table
return (select b from t1)
go
create function dbo.uf_b()
returns table
return (select b from t2)
go
create view dbo.v
as
select a = 1, b from dbo.uf_a()
union all
select a = 2, b from dbo.uf_b()
go
-- Plans
set statistics xml on
select * from dbo.v; -- two tables
select * from dbo.v where a = 1; --one table
set statistics xml off
go
drop function dbo.uf_a, dbo.uf_b
go
-- Multistatement
create function dbo.uf_a()
returns @t table(b int) 
as
begin
	insert @t
	select b from t1
	return
end
go
create function dbo.uf_b()
returns @t table(b int) 
as
begin
	insert @t
	select b from t1
	return
end
go
set statistics xml on
select * from dbo.v; -- two tables
select * from dbo.v where a = 1; --two tables
set statistics xml off
go
--clean
drop view dbo.v;
drop function dbo.uf_a, dbo.uf_b
drop table t1,t2;

use tempdb;
go

create table dbo.t1 (id int primary key, v varchar(10));
create table dbo.t2 (id int primary key, v varchar(10));
go

create function dbo.fn1_m
(
 @v varchar(10)
)
returns @r table (id int primary key, v varchar(10))
as
begin
 insert into @r (id, v) select id, v from dbo.t1 where v = @v;
 return;
end
go 

create function dbo.fn2_m
(
 @v varchar(10)
)
returns @r table (id int primary key, v varchar(10))
as
begin
 insert into @r (id, v) select id, v from dbo.t2 where v = @v;
 return;
end
go 

create function dbo.fn3_m
(
 @p int,
 @v varchar(10)
)
returns table
as
return (
 select
  1 as p, fn1.id, fn1.v
 from
  dbo.fn1_m(@v) fn1
 where
  @p = 1

 union all

 select
  2 as p, fn2.id, fn2.v
 from
  dbo.fn2_m(@v) fn2
 where
  @p = 2
);
go

insert into dbo.t1 select top (100) object_id, 't1' from sys.objects;
insert into dbo.t2 select top (100) object_id, 't2' from sys.objects;
go

declare @v varchar(10), @lsn nvarchar(30);
select @lsn = max([Current LSN]) from sys.fn_dblog(null, null)

set statistics xml, io on;
select @v = v from dbo.fn3_m(1, 't1');
set statistics xml, io off;

select
 object_name(c.object_id), count(*) as [rows inserted]
from
 sys.columns c join
 sys.partitions p on p.object_id = c.object_id join
 sys.allocation_units au on au.container_id = p.partition_id join
 sys.fn_dblog(null, null) l on [Current LSN] > @lsn and l.[AllocUnitId] = au.allocation_unit_id and Operation = N'LOP_INSERT_ROWS'
where
 c.name = 'v' and
 object_name(c.object_id) like '#%'
group by
 object_name(c.object_id);
go

drop function dbo.fn1_m, dbo.fn2_m, dbo.fn3_m;
drop table dbo.t1, dbo.t2;
go