SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

T-SQL FizzBuzz

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.

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'

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) =
' +
			CASE WHEN Id % 15 = 0 THEN 'fizzbuzz'
				WHEN Id % 5 = 0 THEN 'buzz'
				WHEN Id % 3 = 0 THEN 'fizz'
				FROM sys.all_columns) FizzBuzz
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


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...