FizzBuzz in T-SQL

February 3, 2009 · 12 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

{ 11 comments… read them below or add one }

Leave a Comment

{ 1 trackback }