Gatekeeper (5/2/2011)
Not to mention his CTE tally table...
Thank you for the wonderful kudo but I absolutely cannot take the credit for the cteTally. The original concept was first published by Itzik Ben-Gan and he used a "base 2" generator. The "base 10" generator that I included in my article was the result of a lot of good people's (Lynn Petis started the ball rolling in this area) efforts here on SSC where we tested many different "bases". I chose to stick with the "base 10" generator because, as with many different based generators, it appeared to have a minor speed advantage over the "base 2" generator, took fewer Cross Joins to ramp up to the larger numbers and, as a result, took a bit less code.
Still, I have to give the credit to Itzik for the wonderful and very useful idea especially since it generates virtually no reads. The use of a physical Tally Table will still beat it by a bit in some applications but you simply have to love the "no reads" aspect of Itzik's fine idea.
--Jeff Moden
Change is inevitable... Change for the better is not.