Jeffrey Williams, the more I look at your code the more I like it.  I definitely missed a few things and using TOP in the select is so obvious now. ...

If the number of queries is increased to 100 then ms_diff of the tally table is only 16 whereas for the recursive CTE it's 2531!

Ok, here's a different comparison.  Suppose the query contains 34 UNION ALL SELECTS from a tvf generating 1000 row values in sequence.

declare
  @start_dt   ...

That's over 100,000 rows.  When it drops to 10,000 the difference is much less.  Then when it drops to 1,000 the difference is so small it's hard to measure.

The first part of the problem is how you are generating the tally - you are generating too many rows.  The second part of the problem is the return...

All right that's nice progress.  Thanks for following up.  Well, an adID is the same thing as a RecordID.  If you recall the earlier query had RecordID in the select...

;with grp_cte as (
    select distinct r.PartIDC, r.PartIDX, r.step
    from #Replacement r
         join...

Submitted the SSC article last night

Progress.  We have 2 videos which are presentation ready.  The Demo video we recorded 5 times and the Quick Start video 3+ times.   It's taken multiple iterations.  Nobody listens to...

Keep in mind that'll update all the batch id's every time which might not be intended or a good idea on a large table.  Adding a WHERE batch_ID IS...

As general issues go does this require ##temp to have global scope?  Also, why make the variables varchar(max)?  I got it to work like this

drop table if...

There was a slight addition needed to David Burrow's code above.  Which is nicely done and way better than the (now Closed) Stack Overflow answer to the same question. ...

• This reply was modified 5 years, 2 months ago by Steve Collins. Reason: typo

Yay!!  Just passed my goal of 1,500 points on Stack Overflow which means I can now create a tag for the JsonAutoService C# data access library.   I began on August...

It could be done with a correlated subquery too.  Stylewise I prefer >= and <= to BETWEEN AND.  Anything that's shorter, I like.  The more framework words you use the...

Something like this

select sa.userID, a.RecordID
from SmartAgents_toSend sas
     join Ads a on sas.adID=a.RecordID
     join SmartAgents sa on a.price between sa.priceFrom...