Sorry for my a bit unorthodox view on " FizzBuzz" problem in T-SQL, but couldn't keep myself quiet :hehe:
I don't think it's a good T-SQL question at all..
Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, and is actually a wonderful teaching tool to show people the difference between RBAR, Hidden RBAR, and Set Based methods that use "Pseudo Cursors". Have them do it with explicit loops, with a recursive CTE, without either of those at least 3 different ways, and then measure and discuss the resource usage and performance differences. So far as I'm concerned, it's one of the more important concepts in SQL Server
Agree on this, but I wouldn't ask specifically for FizzBuzz. Probably question like "Could you show few ways to produce a list of N [sequential] numbers in T-SQL" would do good enough for finding if the man can think in "SETs" and aware of T-SQL features such as CTE's, windowed functions and even IDENTITY function which can produce it together with creating a table while used in SELECT INTO.
If I want to check knowledge of CASE in T-SQL, I would prefer to ask the question where it's need to be used in ORDER BY...
I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression.
Actually, in my experience, there are more people who miss data conversion on number to varchar required in the ELSE part of this CASE...
If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion!
I cannot say that knowing MOD defines you as person who knows the basic Math functions in T-SQL. What makes MOD more basic than ABS or POWER for example?
I do agree that Fizz-Buzz question is a good one for other programming languages, but T_SQL...
Now, the final one: If you really want to make an impression (good or bad) on the interview, when asked such question, then ask interviewer if he want to see common solution using MOD or kind of "native" T-SQL which doesn't need one!
When I've done it myself once, I caused a bit of surprised. Technical interviewer (I think at least 10 years younger than me), in front of his manager, instead of simply asking me to show how, replied back: "It's impossible, how will you check the number can be divided by 3 or 5?" And when I said, that it's not really required in T-SQL, I've been asked to explain myself by the manager. In reality it can be done by few ways. Here I will post just one of them:
;WITH
S1 AS (SELECT 1 N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) A(N)
,(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) B(N))
,Set100
AS
( SELECT ROW_NUMBER() OVER (ORDER BY N) N, 'Fizz' F,'Buzz' B
FROM S1
)
SELECT COALESCE(S15.F + S15.B, S5.B, S3.F, CAST(S1.N AS VARCHAR))
FROM Set100 S1
LEFT JOIN Set100 S3 ON S3.N * 3 = S1.N
LEFT JOIN Set100 S5 ON S5.N * 5 = S1.N
LEFT JOIN Set100 S15 ON S15.N * 15 = S1.N
ORDER BY S1.N
No MOD, No CASE WHEN, just very basic T-SQL joins + COALESCE from ANSI beloved by J.C. 😉 ...
I'm no gonna do discuss if it's slower than CASE WHEN with MOD, that was done just to show, if you really can think in "SET's":hehe: