Why do I get an index spool on a heap in scalar subquery?
0
votes
1
answer
216
views
I was reading [this](https://learn.microsoft.com/en-us/archive/blogs/craigfr/scalar-subqueries)
And I did the following:
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
);
And I got an [index spool](https://www.brentozar.com/pastetheplan/?id=QpaT80Kj8T) , even though the table is a heap. The question is, how did this happen? Why do I get an index spool on a heap? In the example mentioned in the above link, there was no rows, so no spools, but here I see them?
Asked by Suleyman Essa
(167 rep)
Apr 13, 2025, 12:55 PM
Last activity: Apr 13, 2025, 02:08 PM
Last activity: Apr 13, 2025, 02:08 PM