A Set Based FizzBuzz Answer using T-SQL

Just a simple solution to show a set-based answer to the FizzBuzz question.

If you are not familiar with the programming scenario of FizzBuzz, it is losely the following: Write a program that displays the numbers from 1 to 100. If the number is a multiple of three, print "Fizz" instead of the number. If the number is a multiple of five, print "Buzz". For numbers which are multiples of three AND five, print "FizzBuzz".

Most programmers immediately turn to a loop, use a counter and up the counter for each iteration, determine if the number is divisible outlined above, and move to the next number.

In my example, I'm using the master.dbo.spt_values table which holds the range of values we need for this exercise. I normally create date and number tables for just such uses on database servers so as not to rely on system tables.

Aaron Bertrand has a good performance comparison with using numbers tables, date tables and common table expressions (CTE) usage. His Generate a Sequence Without Loops Part I and Part II are good articles to read if you want to dive further into this topic. His Part III of the series dives into comparing the use of number and date tables, as well as using CTEs.