FizzBuzz in T-SQL

February 3, 2009

Pinal Dave posted yesterday about how to solve the Fizz Buzz problem using T-SQL.

Definition of FizzBuzz Puzzle : Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

His solution works, but he is using procedural logic. Some of the biggest causes of performance problems in SQL Server are caused by application developers who try to use procedural logic instead of using the set-based logic that databases are meant for.

Here is how to solve the FizzBuzz problem using set-based logic in T-SQL:

WITH Numbers(Number) AS (
  SELECT 1
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number < 100
)
SELECT
  CASE 
    WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'FizzBuzz'
    WHEN Number % 3 = 0 THEN 'Fizz'
    WHEN Number % 5 = 0 THEN 'Buzz'
    ELSE CONVERT(VARCHAR(3), Number)
  END
FROM Numbers
ORDER BY Number

Tagged as: , , ,

{ 1 trackback }

Another 3 SQL Server bloggers at SQLServerPedia | Brent Ozar - SQL Server DBA
February 6, 2009 at 4:15 am

{ 8 comments… read them below or add one }

Pinal Dave February 3, 2009 at 7:28 pm

Nice

Rhys February 3, 2009 at 7:42 pm

This reminds us how hard is is to keep your mind on SET BASED all the time.

Scott Turner February 5, 2009 at 5:01 am

Rob,

This doesn't seem to scale well.

When I change the where clause to "WHERE Number < 102", I get:

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Scott Turner February 5, 2009 at 5:08 am

Rob,

This also shows another thing. The CTE is recursive in nature. So what we really have here is an example of using recursion.

Rob Boek February 5, 2009 at 5:38 am

That would be because the default MAXRECURSION is 100. If you want to go above that, add OPTION(MAXRECURSION 102) after the ORDER BY clause. You can use 0 for unlimited, but be careful.

Amit April 2, 2009 at 10:12 am

declare @num int,
@n1 int,
@n2 int
set @num = 1

while(@num < = 100)
begin

select @n1 = (@num%3)
select @n2 = (@num%5)

if (@n1 = 0)
if (@n1 = 0 and @n2 = 0)
Print ‘FizzBuzz’
else
Print ‘Fizz’
if (@n1 != 0 and @n2 = 0)
Print ‘Buzz’
else
if (@n1 != 0 and @n2 != 0)
Print @num
set @num = @num + 1
end

Amit April 2, 2009 at 10:13 am

if (@n1 != 0 and @n2 != 0) not required though

Dave Collins February 22, 2010 at 4:34 am

Hi, how about the following for a fully set based solution…

create table #temp
(number int )
go

insert into #temp(number)
select top 100 row_number() OVER (ORDER BY [object_id]) from sys.columns;

select
number
, case
when number % 3 = 0 AND number % 5 = 0 then ‘fizzbuzz’
when number % 3 = 0 then ‘fizz’
when number % 5 = 0 then ‘buzz’
else ” end as test
from #temp;

drop table #temp;

Leave a Comment