Ok here are two recent winning recursive cte's.  Be gentle when evaluating the opposing answers 🙂

https://stackoverflow.com/questions/63922869/get-an-interval-of-dates-from-a-range-of-dates/63923536

https://stackoverflow.com/questions/63919706/fill-missing-months-on-a-date-query/63920844

As a bonus, here losing while using an ordinal splitter

https://stackoverflow.com/questions/63886788/extracting-date-from-text-in-sql-using-cursor/63887736

Here's a DB Fiddle that shows fnNumbers is about 4-10x faster to generate 100,000 rows.

Here's a DB Fiddle that shows fnNumbers is about 2x faster to generate 10,000 rows.

Here's...

It should be said I'm not looking for an advantage not available to anyone else.  As soon as a different method works the others will just switch and then it's...

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, 5 months ago by Steve Collins. Reason: typo