I thought for the end of the month it would be fun to do another T-SQL Puzzle. In this case a slight upgrade of the FizzBuzz puzzle using T-SQL. So here are the rules:

Argh: I made a mistake in my rules so I’m correcting them. New text in italics, removed text struck out.

• Create a table with the columns x, y and z, each representing a coordinate in a cube.
• Each coordinate can have values 1-100 and there will be every possible combination of values. (1,1,1) to (100,100,100).
• A fourth column will be created that contains the Fizz/Buzz/FizzBuzz. Name it FizzBuzz for convinence.
• If any coordinate is evenly divisible by 3 then that the FizzBuzz cell contains “Fizz”.
• If any coordinate is evenly divisible by 5 then that the FizzBuzz cell contains “Buzz”.
• If any coordinate or combination of coordinates is evenly divisible by both 3 and 5 then the FizzBuzz cell contains “FizzBuzz”.

Optional rules to make it hard .. err .. more fun.

• This must be done in a single query. No creating the table & populating it in multiple steps.
• No using the modulo (%) operator.
• It’s going to be a big table so let’s save some space. Use the smallint datatype for the coordinates and of course varchar(8) for the cells.
• Expand to 1000 values per direction (but you had better have a lot of space).

My solution (which I’ll email if you ask me nicely) took ~45 min with all of the … extras. Now I have a moderately slow laptop ($350 when I bought it 4 years ago) with an HDD. I’m also kind of so-so when it comes to T-SQL so I wouldn’t be surprised if you did better than me. As a comparison, the same thing with just 100 values per direction took ~3 seconds.