I've heard the tally table described as the "Swiss Army Knife of SQL" by more than one person on this site. It can solve a lot of problems and avoid the performance degradation of looping constructs. The performance gains you can realize are through the roof. It also has the coolness factor. 😎

Yes, you can build one on the fly, but a lot of people keep a tally table as a permanent table. See Jeff Moden's article Luis referenced above for building one.