-- 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;