Substitute Primary Keys

-- if accidentally left on from previous runs
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;

-- drop if they exist
drop view lAN;
drop view vAt2;
drop function fAt1;
drop table At1;
drop table At2;
drop table AN;

-- create the tables
create table AN (
    id int not null primary key
);

create table At1 (
    id int not null foreign key references AN(id),
    stamp datetime not null,
    primary key (
        id,
        stamp
    )
);

create table At2 (
    id int not null foreign key references AN(id),
    stamp datetime not null,
    primary key (
        id,
        stamp
    )
);
go

-- create the function
create function fAt1 (@version int)
returns table as return
select
    id,
    stamp
from (
    select
        id,
        stamp,
        ROW_NUMBER() over (
            partition by
                id
            order by
                stamp desc
        ) as v
    from
        At1
) t
where
    t.v = @version
go

-- create a view
create view vAt2
as
select
    id,
    stamp,
    ROW_NUMBER() over (
        partition by
            id
        order by
            stamp desc
    ) as v
from
    At2
go

-- create the view
create view lAN
as
select
    an.id as AN_id,
    at1.id as AT1_id,
    at1.stamp as AT1_stamp,
    at2.id as AT2_id,
    at2.stamp as AT2_stamp
from
    AN an
left join
    fAt1(1) at1
on
    at1.id = an.id
left join
    vAt2 at2
on
    at2.id = an.id
and
    at2.v = 1
go

-- populate the tables
declare @numberOfRows int = 1000;

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

declare @now datetime = getdate();
declare @then datetime = dateadd(day, -333, @now);

insert into At1 (id, stamp) select id, @now from AN;
insert into At1 (id, stamp) select id, @then from AN;
insert into At2 (id, stamp) select id, @now from AN;
insert into At2 (id, stamp) select id, @then from AN;
go

SET STATISTICS XML ON;
SET STATISTICS IO,TIME ON;
-------------------------------------------------------------
-- query the view and check if table elimination is in effect
select
    avg(cast(AN_id as bigint))
from
    lAN;

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

select
    avg(cast(AT2_id as bigint))
from
    lAN
where
    AT2_id is not null;
-------------------------------------------------------------
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;