I was looking around for something to write about this evening and came across one of Russ Thomas’ (b/t) old monthly challenges (Feb 2016).

The challenge is two-fold. A FizzBuzz problem is a common programming interview challenge that asks a coder to print the numbers from 1 to 100. Every time a number is divisible by 3, print fizz instead, when it’s divisible by 5, print buzz, and when it’s divisible by both, print fizzbuzz. The real challenge, however, is to do it in as few lines of code as possible and in our case… TSQL… it should also be set-based – (temp tables are ok).

Now normally FizzBuzz is done with a loop, but as Russ said, we are using T-SQL so batch code is always the goal. That said, what table should I query to get the numbers 1-100? Well, I decided I’d just do something simple here and use a system view that’s more than 100 rows, the ROW_NUMBER function, and restrict it using TOP.

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY object_id) Id
FROM sys.all_columns

A numbers table would be easier (and probably faster) but I decided that since it would need to be created (not already part of SQL) it would go against the rules or at least the time spent creating it would count against my performance.

Next I needed to determine what should be printed out. Well modulo and a case statement is prefect for that. A simple case statement checking for Id % 3 = 0, Id % 5 = 0 and Id % 15 = 0 and return fizz, buzz or fizzbuzz respectively. Then an ELSE clause at the end to return the number itself.

CASE WHEN Id % 15 = 0 THEN 'fizzbuzz'
WHEN Id % 5 = 0 THEN 'buzz'
WHEN Id % 3 = 0 THEN 'fizz'
ELSE CAST(Id AS VARCHAR(5)) END

I’m close but now I need to figure out how to display the output. I decided that just running the query didn’t really count. That would be too easy right? The next option is to loop through the result set printing each row out, which again goes against the whole batch thing. Which leads me to a simple piece of code that creates a delimited list. In this case, I’m delimiting by a carriage return so each row goes on its own line.

Now I had a query that created a string that I just needed to print out. I couldn’t just run the thing (same reason as above) so I tried doing a PRINT (query) but that doesn’t work (no subqueries on a print, sorry). So next I decided I needed to put the results of the query (a single string anyway) into a variable, then print the variable. Rather than declaring a variable and setting it in a separate line I combined both steps into a single command, then one more command to print the variable out.

DECLARE @FizzBuzz varchar(max) =
(
STUFF(
(SELECT '
' +
CASE WHEN Id % 15 = 0 THEN 'fizzbuzz'
WHEN Id % 5 = 0 THEN 'buzz'
WHEN Id % 3 = 0 THEN 'fizz'
ELSE CAST(Id AS VARCHAR(5)) END
FROM (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY object_id) Id
FROM sys.all_columns) FizzBuzz
ORDER BY Id
FOR XML PATH, TYPE
).value(N'.[1]',N'nvarchar(max)'),1,1,N'')
);
PRINT @FizzBuzz;

How about performance you might ask? Running this against 100 rows it was near instant (<5ms). So I tried again against a few more rows. At 9500 rows it was still under 5 ms. One of the downsides of this method is needing a table to run against with sufficient rows to cover the number of lines required and 9500 was about the limit to this table. So I joined sys.all_columns against itself to go higher. When I got up to 100k rows I finally got a reasonable amount of time. ~150ms.

The other down side is that I can’t print the whole thing at once. The PRINT command can only print 8000 characters at a time. This means that if I’m going to work with more than ~2000 rows (rough average of 4 characters a row) at a time I’m going to have to use multiple print statements with substrings.

Do you have a t-sql fizzbuzz solution? Is it better than mine? I’d love to see it.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL