No table elimination for window functions

-- 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 lAN_CTE;
drop view vAt2;
drop view vAt5;
drop function fAt1;
drop function fAt4;
drop table At1;
drop table At2;
drop table At3;
drop table At4;
drop table At5;
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
    )
);

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

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

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

-- create the functions
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 function fAt4 (@version int)
returns @t table (
    id int not null primary key,
    stamp datetime not null
) as
begin
    insert into @t
    select
        id,
        stamp
    from (
        select
            id,
            stamp,
            ROW_NUMBER() over (
                partition by
                    id
                order by
                    stamp desc
            ) as v
        from
            At4
    ) t
    where
        t.v = @version;
    return
end
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 vAt5
as
select
    id,
    stamp,
    lag(stamp, 1, stamp) over (partition by id order by stamp desc) as stamp_prev
from
    At5
go

-- create the latest 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,
    at3.id as AT3_id,
    at3.stamp as AT3_stamp,
    at4.id as AT4_id,
    at4.stamp as AT4_stamp,
    at5.id as AT5_id,
    at5.stamp as AT5_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
left join
    At3 at3
on
    at3.id = an.id
and
    at3.stamp = (
        select
            sub.stamp
        from
            At3 sub
        where
            sub.id = an.id
        order by
            sub.stamp desc
        offset 0 rows fetch next 1 rows only
    )
left join
    fAT4(1) at4
on
    at4.id = an.id
left join
    vAt5 at5
on
    at5.id = an.id
and
    at5.stamp = (
        select
            sub.stamp
        from
            vAt5 sub
        where
            sub.id = an.id
        and
            sub.stamp = sub.stamp_prev
    );

go

create view lAN_CTE
as
with
a1 as (
    select TOP (2147483647)
        *
    from
        fAt1(1)
    order by
        id asc
),
a2 as (
    select
        *
    from
        vAt2
    where
        v = 1
),
a3 as (
    select
        *
    from
        at3
    where
        at3.stamp = (
            select
                sub.stamp
            from
                At3 sub
            where
                sub.id = at3.id
            order by
                sub.stamp desc
            offset 0 rows fetch next 1 rows only
        )
),
a4 as (
    select
        *
    from
        fAt4(1)
),
a5 as (
    select
        *
    from
        vAt5
    where
        stamp = stamp_prev
)
select
    an.id as AN_id,
    (select id from a1 where a1.id = an.id) as AT1_id,
    (select stamp from a1 where a1.id = an.id) as AT1_stamp,
    (select stamp from a1 where a1.id = an.id) as AT1_stamp_again,
    (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 id from a3 where a3.id = an.id) as AT3_id,
    (select stamp from a3 where a3.id = an.id) as AT3_stamp,
    (select id from a4 where a4.id = an.id) as AT4_id,
    (select stamp from a4 where a4.id = an.id) as AT4_stamp,
    (select id from a5 where a5.id = an.id) as AT5_id,
    (select stamp from a5 where a5.id = an.id) as AT5_stamp
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 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;
insert into At3 (id, stamp) select id, @now from AN;
insert into At3 (id, stamp) select id, @then from AN;
insert into At4 (id, stamp) select id, @now from AN;
insert into At4 (id, stamp) select id, @then from AN;
insert into At5 (id, stamp) select id, @now from AN;
insert into At5 (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
    'Anchor id, left joined view',
    avg(cast(AN_id as bigint))
from
    lAN;

select
    'Anchor id, CTEs',
    avg(cast(AN_id as bigint))
from
    lAN_CTE;


------
select
    'AT1 id, left joined view',
    avg(cast(AT1_id as bigint))
from
    lAN
where
    AT1_id is not null;

select
    'AT1 id, CTEs',
    avg(cast(AT1_id as bigint))
from
    lAN_CTE
where
    AT1_id is not null;

select
    'AT1 id, AT1 stamp, CTEs',
    avg(cast(AT1_id as bigint)),
    min(AT1_stamp)
from
    lAN_CTE
where
    AT1_id is not null;

select
    'AT1 id, AT1 stamp, AT1 stamp again, CTEs',
    avg(cast(AT1_id as bigint)),
    min(AT1_stamp),
    min(AT1_stamp_again)
from
    lAN_CTE
where
    AT1_id is not null;

------
select
    'AT2 id, left joined view',
    avg(cast(AT2_id as bigint))
from
    lAN
where
    AT2_id is not null;

select
    'AT2 id, CTEs',
    avg(cast(AT2_id as bigint))
from
    lAN_CTE
where
    AT2_id is not null;

select
    'AT2 id, AT2 stamp, CTEs',
    avg(cast(AT2_id as bigint)),
    min(AT2_stamp)
from
    lAN_CTE
where
    AT2_id is not null;

------
select
    'AT3 id, left joined view',
    avg(cast(AT3_id as bigint))
from
    lAN
where
    AT3_id is not null;

select
    'AT3 id, CTEs',
    avg(cast(AT3_id as bigint))
from
    lAN_CTE
where
    AT3_id is not null;

select
    'AT3 id, AT3 stamp, CTEs',
    avg(cast(AT3_id as bigint)),
    min(AT3_stamp)
from
    lAN_CTE
where
    AT3_id is not null;


------
select
    'AT4 id, left joined view',
    avg(cast(AT4_id as bigint))
from
    lAN
where
    AT4_id is not null;

select
    'AT4 id, CTEs',
    avg(cast(AT4_id as bigint))
from
    lAN_CTE
where
    AT4_id is not null;

select
    'AT4 id, AT4 stamp, CTEs',
    avg(cast(AT4_id as bigint)),
    min(AT4_stamp)
from
    lAN_CTE
where
    AT4_id is not null;

------
select
    'AT5 id, left joined view',
    avg(cast(AT5_id as bigint))
from
    lAN
where
    AT5_id is not null;

select
    'AT5 id, CTEs',
    avg(cast(AT5_id as bigint))
from
    lAN_CTE
where
    AT5_id is not null;

select
    'AT5 id, AT5 stamp, CTEs',
    avg(cast(AT5_id as bigint)),
    min(AT5_stamp)
from
    lAN_CTE
where
    AT5_id is not null;
-------------------------------------------------------------
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;