Grant Fritchey (7/7/2015)
I'm just surprised you didn't get a table spool in 2008 with a recursive CTE. That's pretty common.
A recursive CTE plan always features spools. It's not a recursive CTE:
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
The spool is due to the NOT IN. Using NOT EXISTS might yield a better plan:
select
c.lineID,
sum(rc),
sum(copay),
sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln c
where c.[status] is null
and NOT EXISTS (SELECT 1 FROM unpaid u WHERE u.claimID = c.lineID)
group by c.lineID
Edit: I'm an idiot today.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden