Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1.  
  2. SET STATISTICS XML OFF;
  3. SET STATISTICS IO,TIME OFF;
  4.  
  5. --select @@VERSION;
  6. drop view lAN_forever;
  7. drop view lAN_quickly;
  8. drop function fAT_with_pk;
  9. drop function fAT_without_pk;
  10. drop table AT1;
  11. drop table AT2;
  12. drop table AN;
  13.  
  14. create table AN (
  15.     id int not null
  16. );
  17. go
  18.  
  19. create table AT1 (
  20.     id int not null
  21. );
  22. go
  23.  
  24. create table AT2 (
  25.     id int not null
  26. );
  27. go
  28.  
  29. create function fAT_with_pk (
  30.     @doNotLoopForever bit
  31. )
  32. returns @AT table  (
  33.     id int not null primary key
  34.     -- with the primary key definition
  35.     -- the optimizer should be able
  36.     -- to do table elimination and
  37.     -- in that case never materialize
  38.     -- this table
  39. ) as
  40. begin
  41.     declare @i int = 0;
  42.     while(@i >= @doNotLoopForever)
  43.     begin
  44.         set @i = @i + 1;
  45.         insert into @AT values(@i);
  46.     end
  47.     return
  48. end
  49. go
  50.  
  51. create function fAT_without_pk (
  52.     @dummy bit
  53. )
  54. returns @AT table  (
  55.     id int not null
  56. ) as
  57. begin
  58.     insert into @AT (id)
  59.     select id from AT1;
  60.     return
  61. end
  62. go
  63.  
  64. create view lAN_forever
  65. as
  66. select
  67.     an.id as AN_id,
  68.     at1.id as AT1_id,
  69.     at2.id as AT2_id,
  70.     at3.id as AT3_id
  71. from
  72.     AN an
  73. left join
  74.     AT1 at1
  75. on
  76.     at1.id = an.id
  77. left join
  78.     fAT_with_pk(0) at2
  79. on
  80.     at2.id = an.id
  81. left join
  82.     fAT_without_pk(0) at3
  83. on
  84.     at3.id = an.id;
  85. go
  86.  
  87. create view lAN_quickly
  88. as
  89. select
  90.     an.id as AN_id,
  91.     at1.id as AT1_id,
  92.     at2.id as AT2_id,
  93.     at3.id as AT3_id
  94. from
  95.     AN an
  96. left join
  97.     AT1 at1
  98. on
  99.     at1.id = an.id
  100. left join
  101.     fAT_with_pk(1) at2
  102. on
  103.     at2.id = an.id
  104. left join
  105.     fAT_without_pk(0) at3
  106. on
  107.     at3.id = an.id;
  108. go
  109.  
  110.  
  111. declare @numberOfRows int = 1000;
  112.  
  113. with rowGen(rowNum) as (
  114.     select
  115.         1
  116.     union all
  117.     select
  118.         rowNum + 1
  119.     from
  120.         rowGen
  121.     where
  122.         rowNum < @numberOfRows
  123. )
  124. insert into AN (id)
  125. select rowNum from rowGen
  126. option (maxrecursion 0);
  127.  
  128. insert into AT1 (id) select id from AN;
  129. insert into AT2 (id) select id from AN;
  130.  
  131. alter table AN add constraint pkAN primary key (id);
  132. alter table AT1 add constraint pkAT1 primary key (id);
  133. alter table AT1 add constraint fkAT1 foreign key (id) references AN(id);
  134.  
  135. update statistics AN;
  136. update statistics AT1;
  137. update statistics AT2;
  138. go
  139.  
  140. SET STATISTICS XML ON;
  141. SET STATISTICS IO,TIME ON;
  142. -------------------------------------
  143. select
  144.     avg(cast(AT1_id as bigint))
  145. from
  146.     lAN_quickly
  147. where
  148.     AT1_id is not null;
  149. -- this should execute and not run
  150. -- forever due to the infinite loop
  151. select
  152.     avg(cast(AT1_id as bigint))
  153. from
  154.     lAN_forever
  155. where
  156.     AT1_id is not null;
  157. -------------------------------------
  158. SET STATISTICS XML OFF;
  159. SET STATISTICS IO,TIME OFF;
  160. go