TSQL Quiz

I was reading the latest Simple-Talk email that linked me to an article on Coding Horror, "Why Can't Programmers - Program?" In the article they talked about a simple test that they gave developers to verify their abilities and decide whether or not to continue the interview. Here's the test:

Write code that counts from 1 to 100

For each number evenly divisible by 3, substitute 'Bizz'

For each number evenly divisible by 5, substitute 'Buzz'

For each number divisible by both substitute 'BizzBuzz'

I decided to try it out in TSQL. Once I had a solution (it took about five minutes, it would have been about two but I kept getting syntax errors on the CASE statement). I had so much fun that I sent it out to our team to see who else could meet the requirements and I added one more: no cursors are allowed

Before you read any further, try it out for yourself. It's not hard. A solution should take you less than 10 minutes.

Here are the various solutions that we came up with on our team.

First up, mine:

DECLARE @i VARCHAR(3) ;
DECLARE @s VARCHAR(8) ;
SET @i = 1 ;
WHILE @i < 101
    BEGIN
        SELECT  @s = CASE WHEN ( ( @i % 3 = 0 )
                                 AND ( @i % 5 = 0 )
                               ) THEN 'BizzBuzz'
                          WHEN ( @i % 3 = 0 ) THEN 'Bizz'
                          WHEN ( @i % 5 = 0 ) THEN 'Buzz'
                          ELSE @i
                     END ;
        PRINT @s ;
        SET @i = @i + 1 ;
    END ;

I didn't need to use the @s variable for the print statements, but overall, first pass, it was simple and worked fine. I'm assuming I'd still get interviewed although my pay scale may have dropped a grade because I introduced parameters that weren't really necessary.

The next submission came in from Chris:

declare @i int
set @i = 1
while( @i < 101)
 begin
if @i%3 = 0 print 'Bizz'
else if @i%5 = 0 print 'Buzz'
else if @i%3 = 0 and @i%5 = 0 print 'BizzBuzz'
else print @i

set @i = @i+1
 end

He fixed my problem with the string, but he introduced a new one. Go back, reread the code and see if you can spot it. Give up? Because he checked for 3 then 5 then the combination, none of his code found the combination of 3 and 5. End of interview. Sorry Chris. It's also worth mentioning that the CASE statement resolves in a single pass where as the IF statements check each condition. Take a look at the estimated query plans:

Next came one from Det:

CREATE TABLE Nums ( num int primary key )
GO
DECLARE @i int
SET @i = 1
WHILE @i <= 100
    BEGIN
        INSERT  Nums ( num )
        VALUES  ( @i )
        SET @i = @i + 1
    END
 
SELECT  CASE WHEN num % 3 = 0
                  AND num % 5 = 0 THEN 'BizzBuzz'
             WHEN num % 3 = 0 THEN 'Bizz'
             WHEN num % 5 = 0 THEN 'Buzz'
             ELSE CAST(num AS nvarchar)
        END
FROM    nums

Det's worked very well, but he created a permanent table and didn't include a drop statement. When I went back to look at the query plan, I got an error. We'll continue the interview, but Det shouldn't count on getting a top spot on the team. He submitted a second query as well:

DECLARE @i int
SET @i = 1
WHILE @i <=100
BEGIN
SELECT CASE WHEN @i % 3 = 0  AND @i % 5 = 0 THEN 'BizzBuzz'
            WHEN @i %3 = 0 THEN 'Bizz'
            WHEN @i %5 = 0 THEN 'Buzz'
            ELSE CAST(@i AS nvarchar) END
SET @i = @i + 1
END

This worked in a repeatable fashion and instead of printing output lines to the message window, it returned actual results. 100 separate result sets, but even so, it worked. Points for perseverance.

Next came Scott Abrants:

DECLARE @int AS INT;
DECLARE @immdResult AS VARCHAR(100);
SET @int = 1;
WHILE @int < 101
BEGIN
    SET @immdResult = CASE WHEN @int % 3 = 0
                                AND @int % 5  0 THEN 'Bizz'
                           WHEN @int % 5 = 0
                                AND @int % 3  0 THEN 'Buzz'
                           WHEN @int % 3 = 0
                                AND @int % 5 = 0 THEN 'BizzBuzz'
                      END ;
    PRINT 'The number is ' + CONVERT(VARCHAR(10), @int);
    IF LEN(@immdResult) > 0 
        BEGIN
            PRINT @immdResult;
        END
    SET @int = @int + 1;
END;

Not at all surprisingly, Scott's is dead on accurate and extremely thorough. He reintroduced the extra parameter that I had, but instead of a crutch, he makes real use of it. At this point Scott's at the head of the class

Freely admitted, this is a silly little test and doesn't really demonstrate much in the way of coding skill, or more especially TSQL skills. However, it spawned a lot of discussion within the team. We started running the queries to see what performance looked like. Since most of them didn't do anything approaching data manipulation, there really weren't query plans to look at. Performance was very similar to a degree:

Chris - 15ms

Grant - 31ms

Scott - 46ms

Det 1 - 62ms

Det 2 - 93ms

One of the discussions that came up was, shouldn't there be a simple way to do this with a CTE? So after lunch, I set out to try it. This is what I came up with:

WITH Nbrs(n) AS (
SELECT 1 
UNION ALL
SELECT 1 + n FROM Nbrs WHERE n 
This worked well, looks neat, and it ran in 15ms. The only problem with it was, it created one scary looking query plan:



That's it. We had some fun writing these queries and I decided to share them. The results speak for themselves. There is no great and magnificent truth revealed here. It's simply a starting point for conversation and a method of verifying a basic level of knowledge along with the ability to apply logic. There are probably much more elegant ways to do this. I looked up some methods for generating counting data that could be used for something like this or for something a heck of a lot more sophisticated like a Numbers table and found a nice summary of information here. If you have a better way to skin this cat, please share it. If you're applying at my company, you might want to anticipate this as a question at the start of the interview.