SQL Server Iterations with the Tally Table

SQL Server Central recently posted an article by Jeff Moden on using a Tally Table to iterate through database rows rather than a WHILE loop or CURSOR. The article defines tally tables and explains both how to create them and use them. Jeff even shows performance comparisons with a WHILE loop.

For those unfamiliar with tally tables, a tally table is generally used to parse data stored in a comma-delimited format but can also be used for other iterative actions. The tally table iterations also use set math and thus keep the CPU usage and row counts low consistently.

The next time you find yourself needing to iterate through table rows, try using a tally table.

Advertisements