Jeff Moden (2/17/2014)
zulfansari (2/17/2014)
Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..
I really appreciate your help and learned new tips and tricks from all of you..
God bless you all!
Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..
Best Regards,
Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."
I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.
Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.
Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.
Hat's off to both Dwain and Jason for pumping out the code.
Who is Red Green? Any relation to Opal Azure?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St