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 }

{ 1 trackback }