In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?

use tempdb;

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
select top (1000)
row_number() over (order by 1/0)
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
select top (10000)
row_number() over (order by 1/0) + 10000
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

sum(a.v + b.v)
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;

drop table dbo.t1, dbo.t2;

enter image description here

It's reproducible in all versions starting from 2008 R2 (I just have no server with earlier versions to test)


It's a somewhat annoying limitation of the current optimizer.

I wrote about this a little as part of The Eager Index Spool and The Optimizer:

Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

  • Thank you. I thought it was a bug – sepupic Nov 22 at 10:33

Because you order by [v], while your primary key is on [id].

BTW: the query could become slightly faster, when you use SUM(a.v) + ISNULL(SUM(b.v), NULL) instead of sum(a.v + b.v).

On the other hand the result could vary a little bit in this case, since you use OUTER APPLY (similar to a LEFT JOIN), so when there is no record found for the "table" [b], it would ignore the a.v in the original query too (since a.v + NULL is NULL) while a.v it would be in the total sum of my suggested version (it's your data, so you have to decide, if you remain on the sum(a.v + b.v) it would be a good target for a comment saying, that this is the intended behavior :-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.