Dave + SQL Server = Ingenious SQL….

Introduction:

In my very first Technical Blog I am going to show how one can accomplish the Fizz Buzz Challenge a few different ways using T-SQL. Fizz Buzz is a popular interview question that will allow an interviewee to demonstrate basic programming knowledge. This is normally done in a programming language such as C# or Java. But today we are going to implement Fizz Buzz using T-SQL…

Background:

A friend, Rick Krueger ( @DataOgre | premierapps.com ), and I have recently decided to give back to the SQL Community by starting to Tweet, Blog, and Present at Local PASS Meetings as well as SQL Saturday events. We had come up with a potential presentation where we needed a Developer and a DBA, the question was who is the DBA and who is the Developer. So a week or so had passed and I found myself with a locked account and a significant amount of wasted time with the help desk.

Rick noticed my frustration and asked the following:

• (rick) Why on earth did they need to log onto your box to reset something on the server?
• (me) Not sure, I guess he didnt believe me when I said my account was locked and he had to try for himself?
• (rick) Did you try FizzBin?
• (rick) http://www.hanselman.com/blog/FizzBinTheTechnicalSupportSecretHandshake.aspx
• (me) Nope, but I wish I would have…
• (rick) Ever hear of FizzBuzz a good interview question…
• (rick) How would you do on that one?

Now any good developer will know who Scott Hanselman is and has followed his work with .Net and MVC… And being that I came from the development world I know who Hanselman is. So I quickly created a C#.net example and sent it off to Rick.

Fizz Buzz:

• Output the numbers from 1 to 100.
• When a number is a multiple of three output “Fizz”.
• When a number is a multiple of five output “Buzz”.
• When a number is a multiple of both three and five output “FizzBuzz”
• When a number is not a multiple of either three or five output the number

C# Code of FizzBuzz:

for(int i = 1; i <= 100; i++){
   if(i % 3 == 0) 
      Console.Write("Fizz "); 
   if(i % 5 == 0) 
      Console.Write("Buzz "); 
   if((i % 3 > 0) && (i % 5 > 0)) 
      Console.Write(i); 
   Console.WriteLine(""); 
}

Bamboozled:

So I asked Rick how he would have implemented the same code to which he simply replied ‘T-SQL’. Without missing a beat, he proceeded to tell me that we now know who the developer and who the DBA is for our presentations…. I was bamboozled into writing C#.net code…

T-SQL:

Below are some T-SQL examples of the Fizz Buzz code. Obviously there are many more ways to write it, but here are a couple I wrote to redeem myself in the DB community…

The example below implements a while loop that populates a table, using the Fizz Buzz logic.

DECLARE @FizzBuzzTable TABLE(Number int, FizzBuzz varchar(10));
DECLARE @i int = 1;
WHILE @i <= 100
BEGIN
 INSERT INTO @FizzBuzzTable
   (Number, FizzBuzz)
 SELECT @i, FizzBuzz = CASE WHEN @i % 3 = 0 AND @i % 5 = 0 THEN 'Fizz Buzz'
   WHEN @i % 3 = 0 THEN 'Fizz'
   WHEN @i % 5 = 0 THEN 'Buzz'
   ELSE CAST(@i AS varchar(3)) END
 SET @i += 1
END
SELECT Number, FizzBuzz FROM @FizzBuzzTable FBT

Using a while loop to insert records may not be the best approach, so the example below implements a recursive CTE to gain 100 values, then applies the Fizz Buzz Logic query.

 ;WITH FizzBuzzTable( Number ) AS (
 SELECT 1 UNION ALL
 SELECT 1 + Number FROM FizzBuzzTable WHERE Number < 100
 )
 SELECT FizzBuzz= CASE WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'Fizz Buzz'
   WHEN Number % 3 = 0 THEN 'Fizz'
   WHEN Number % 5 = 0 THEN 'Buzz'
   ELSE CAST(Number AS varchar(3)) END
 FROM FizzBuzzTable

Using a recursive CTE can have performance issues, so the example below uses SQL written by Ben Itzak to gain 256 values, then applies the Fizz Buzz Logic query.

;WITH L1 AS (SELECT V1 = 1 UNION ALL SELECT V1 = 1) -- 2
 , L2 AS (SELECT V1 = 1 FROM L1 A CROSS APPLY L1 B)  -- 4
 , L3 AS (SELECT V1 = 1 FROM L2 A CROSS APPLY L2 B)  -- 16
 , L4 AS (SELECT V1 = 1 FROM L3 A CROSS APPLY L3 B)  -- 256
 , FizzBuzzTable AS (SELECT TOP 100 Number = ROW_NUMBER() OVER (ORDER BY V1) FROM L4)
 SELECT FizzBuzz = CASE WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'Fizz Buz'
   WHEN Number % 3 = 0 THEN 'Fizz'
   WHEN Number % 5 = 0 THEN 'Buzz'
   ELSE CAST(Number AS varchar(3)) END
 FROM FizzBuzzTable