Multistatement Table-valued Functions and Table Elimination

drop view lAN;
drop function fAT3;
drop function fAT2;
drop table AT1;
drop table AT2;
drop table AN;

create table AN (
    id int not null
);
go

create table AT1 (
    id int not null
);
go

create table AT2 (
    id int not null
);
go

create function fAT2 (
    @dummy int
)
returns @AT table  (
    id int not null primary key
    -- with the primary key definition
    -- the optimizer should be able
    -- to do table elimination
) as
begin
    insert into @AT (id)
    select id from AT2;
    return
end
go

create function fAT3 (
    @dummy int
)
returns @AT table  (
    id int not null
    -- without the primary key definition
    -- there is no way for the optimizer
    -- to know if the join will multiply
) as
begin
    insert into @AT (id)
    select id from AT2;
    return
end
go

create view lAN
as
select
    an.id as AN_id,
    at1.id as AT1_id,
    at2.id as AT2_id,
    at3.id as AT3_id
from
    AN an
left join
    AT1 at1
on
    at1.id = an.id
left join
    fAT2(0) at2
on
    at2.id = an.id
left join
    fAT3(0) at3
on
    at3.id = an.id;
go

with rowGen(rowNum) as (
    select
        1
    union all
    select
        rowNum + 1
    from
        rowGen
    where
        rowNum < 100000
)
insert into AN (id)
select rowNum from rowGen
option (maxrecursion 0);

insert into AT1 (id) select id from AN;
insert into AT2 (id) select id from AN;

alter table AN add constraint pkAN primary key (id);
alter table AT1 add constraint pkAT1 primary key (id);
alter table AT1 add constraint fkAT1 foreign key (id) references AN(id);

update statistics AN;
update statistics AT1;
update statistics AT2;
go

SET STATISTICS XML ON;
-------------------------------------
select
    avg(cast(AN_id as bigint))
from
    lAN;
go

select
    avg(cast(AT1_id as bigint))
from
    lAN
where
    AT1_id is not null;
go

select
    avg(cast(AT2_id as bigint))
from
    lAN
where
    AT2_id is not null;
go

select
    avg(cast(AT3_id as bigint))
from
    lAN
where
    AT3_id is not null;
go
-------------------------------------
SET STATISTICS XML OFF;