Consolidate Execution Plan Paths

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

-- which version?
select @@VERSION;

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

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


create table At2 (
    id int not null foreign key references AN(id),
    stamp datetime not null,
    primary key (
        id,
        stamp
    )
);
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 view lAN
as
with a2 as (
    select
        *
    from
        vAt2
    where
        v = 1
)
select
    an.id as AN_id,
    (select id from a2 where a2.id = an.id) as AT2_id,
    (select stamp from a2 where a2.id = an.id) as AT2_stamp,
    (select stamp from a2 where a2.id = an.id) as AT2_stamp_again
from
    AN an;
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 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;
-------------------------------------------------------------
select
    avg(cast(AT2_id as bigint)),
    min(AT2_stamp),
    min(AT2_stamp_again)
from
    lAN
where
    AT2_id is not null;
-------------------------------------------------------------
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;