Fizzbuzz

5

September 25, 2009 by Mike Hillwig

I recall reading Brent Ozar’s post on questions to ask DBAs in an interview.

Unable to sleep last night, I was trying to solve the Fizzbuzz problem in my head. I knew it could be done with T-SQL, but I hadn’t given it too much thought.

I’m lying in bed mapping out the code in my head. First, I would create a table containing a primary key of integers from 1 to 100. Then I would run several update statements to set the second column to what would be displayed.

I fell asleep thinking I had solved the problem fairly well.  This morning, I sat down in front of my computer at work, wanting to test my plan. Suddenly, it hit me.  I had made the solution much more complicated than it needed to be.

Dear readers, I give you my solution in twelve lines of code.

declare @i int
select @i = 1
while @i <= 100
BEGIN
PRINT CASE
WHEN @i %3 = 0 AND @i % 5 = 0 THEN ‘FIZZBUZZ’
WHEN @i % 3 = 0 THEN ‘FIZZ’
WHEN @i % 5 = 0 THEN ‘BUZZ’
ELSE CAST(@i AS NVARCHAR(30))
END
select @i = @i + 1
END

I’m always looking for a better way of doing things. While this is a much more elegant solution than I had originally found, I’m wondering if it can be any simpler.

5 thoughts on “Fizzbuzz

  1. Brent Ozar says:

    Nice job. This was the kind of solution I wanted to see. I got a little concerned when readers started emailing me some solutions that made me cringe, hahaha.

  2. http://benchmarkitconsulting.com/colin-stasiuk/2009/02/05/sql-2008-compound-operators-and-some-fizzbuzz/

    My FizzBuzz solution… I worked it into a blog post on Inline Variable Assignment and Compound Operators

    If you use Inline Variable Assignment you can cut your code down by 1 line LOL

    declare @i int
    select @i = 1

    becomes

    declare @i int = 1

    nice work! 🙂

  3. If you want to see solutions to this in TSQL, read this article and, more importantly, all the comments afterwards:
    http://www.sqlservercentral.com/articles/SQL+Puzzles/2973/

  4. Rich Bartho says:

    I used this actually to help interview a replacement for myself before and very often, the interviewee also made it more complex than they needed. Usually it was because of one of 3 reason…they were nervous, they wanted to try and show off at how much SQL they knew or they just didn’t know.

    I gave my previous boss (IT director that didnt know anything about SQL) the same solution you came up with in case they wanted to use that question for future interviews. I love that question because it really does separate the good candidates from the less than desirable ones.

  5. […] across Mike Hillwig’s post in response to a question posed by Brent Ozar.  Depending on how literally you want to […]

Leave a Reply

Your email address will not be published. Required fields are marked *