FizzBuzz in T-SQL

February 3, 2009 · 10 comments

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

Update 3/15/2010:
Here is another, much faster solution, taken from ideas I found here.

DECLARE @num INT = 1000000
 
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
 
;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)
 
SELECT TOP (@num)
  Number,
  CASE 
    WHEN Number % 15 = 0 THEN 'FizzBuzz'
    WHEN Number % 3 = 0 THEN 'Fizz'
    WHEN Number % 5 = 0 THEN 'Buzz'
    ELSE CONVERT(VARCHAR(7), Number)
  END AS FizzBuzz
INTO #FizzBuzz
FROM Numbers
 
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
 
--SELECT FizzBuzz FROM #FizzBuzz ORDER BY Number
 
DROP TABLE #FizzBuzz

Tagged as: , , ,

{ 9 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;

Rob Boek March 15, 2010 at 10:18 am

Just updated the post with a much faster solution. There are many more solutions over at http://ask.sqlservercentral.com/questions/4241/whats-the-best-way-to-solve-the-fizzbuzz-question/.

Leave a Comment

{ 1 trackback }