The "Numbers" or "Tally" Table: What it is and how it replaces a loop
There are dozens of things we do in T-SQL which require some type of iteration. "Iteration" means "counters", "loops" and recursion to most people. To those well versed in the “Black Arts” of high performance "Set-based" programming, it means a "Numbers" or "Tally" Table, instead. This “Developer-to-Developer” session starts with the basics of how SQL Server can be tricked into counting faster than you can imagine, how that simple act of counting replaces slothful loops and other forms of “RBAR”, and provides a substantial introduction to “set based thinking”. Code to generate a permanent Tally Table and a refined CTE version of the Tally Table will be demonstrated and compared to a While loop for performance and ease of coding. The session is filled with code demos of how the Tally Table can be used to replace certain While Loops and also includes tips on using CROSS APPLY and INLINE TABLE VALUED FUNCTIONS (iTVFs) to make application of the Tally Table run even faster.
Sr. Application DBA
With more than 22,000 posts, Jeff Moden is the leading poster on SQLServerCentral.com where he coined the term “RBAR” which is pronounced “ree-bar” and is a “Modenism” for “Row By Agonizing Row”. Jeff is mostly self trained in what he calls the “Black Arts” of T-SQL and SQL Server and is known worldwide for his informative articles, his high performance T-SQL coding methods, and his friendly mentoring. His dedication to helping people write better and faster T-SQL have earned him the Microsoft SQL Server MVP award for the last three years.
Register Now for PASS Summit 2010!