Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET STATISTICS XML OFF;
- SET STATISTICS IO,TIME OFF;
- --select @@VERSION;
- drop view lAN_forever;
- drop view lAN_quickly;
- drop function fAT_with_pk;
- drop function fAT_without_pk;
- 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 fAT_with_pk (
- @doNotLoopForever bit
- )
- returns @AT table (
- id int not null primary key
- -- with the primary key definition
- -- the optimizer should be able
- -- to do table elimination and
- -- in that case never materialize
- -- this table
- ) as
- begin
- declare @i int = 0;
- while(@i >= @doNotLoopForever)
- begin
- set @i = @i + 1;
- insert into @AT values(@i);
- end
- return
- end
- go
- create function fAT_without_pk (
- @dummy bit
- )
- returns @AT table (
- id int not null
- ) as
- begin
- insert into @AT (id)
- select id from AT1;
- return
- end
- go
- create view lAN_forever
- 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
- fAT_with_pk(0) at2
- on
- at2.id = an.id
- left join
- fAT_without_pk(0) at3
- on
- at3.id = an.id;
- go
- create view lAN_quickly
- 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
- fAT_with_pk(1) at2
- on
- at2.id = an.id
- left join
- fAT_without_pk(0) at3
- on
- at3.id = an.id;
- go
- 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);
- 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;
- SET STATISTICS IO,TIME ON;
- -------------------------------------
- select
- avg(cast(AT1_id as bigint))
- from
- lAN_quickly
- where
- AT1_id is not null;
- -- this should execute and not run
- -- forever due to the infinite loop
- select
- avg(cast(AT1_id as bigint))
- from
- lAN_forever
- where
- AT1_id is not null;
- -------------------------------------
- SET STATISTICS XML OFF;
- SET STATISTICS IO,TIME OFF;
- go
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement