Eu estava lendo isso e fiz o seguinte:
go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query
select *
from fiirst
where fiirst.col1 > (
select min(seecond.col1)
from seecond
where seecond.col2 < fiirst.col2
);
E eu obtive um spool de índice , mesmo a tabela sendo um heap. A questão é: como isso aconteceu? Por que eu obtenho um spool de índice em um heap? No exemplo mencionado no link acima, não havia linhas, então não havia spools, mas aqui eu os vejo?
