FizzBuzz in SQL--no loops

Question

MS SQL Server, no loops. ;)

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

Execution time ~5ms.

With performance being the most important concern, could this be made better?

Answer 1

Taking the suggestion by @usr to avoid recursive CTEs, I came up with the following formulation:

WITH R4 AS (
    SELECT col FROM (VALUES (1), (2), (3), (4)) x(col)
), R16 AS (
    SELECT a.col
        FROM R4 AS a CROSS JOIN R4 AS b
), R100 AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.col) AS n
        FROM R16 AS a CROSS JOIN R16 AS b
)
SELECT CASE
        WHEN n % 15 = 0 THEN 'FizzBuzz'
        WHEN n %  3 = 0 THEN 'Fizz'
        WHEN n %  5 = 0 THEN 'Buzz'
        ELSE CAST(n AS NVARCHAR(8))
       END AS FizzBuzz
    FROM R100
    ORDER BY n;

I've found that SQL Fiddle is a poor benchmarking platform, as execution times there are completely erratic. Since you want to run this on MS SQL Server, though, we can use Stack Exchange Data Explorer. ☺

Here's your original query and the my non-recursive formulation. Both take 3 ms on Stack Exchange Data Explorer when the results are not cached.

Answer 2

Recursive CTEs in SQL Server execute involving a temp table. This is probably why this tiny amount of work even takes a measurable amount of time. This should take <= 1ms.

Use a numbers table. Or, use one of the many tricks to materialize a sequence of numbers without table access such as:

SELECT Num
FROM (VALUES (1), (2), ...) x(Num)

---

Using Number % 15 = 0 instead of Number % 3 = 0 AND Number % 5 = 0 is less clear. It requires mathematical insight to convince anyone that this is even correct. Code should be obviously correct.

---

The default length of varchar is unclear. (Can you tell from memory?!) Better use nvarchar(400). By default, use Unicode characters in order to just never have certain bugs and problems.

---

These issues aside this query is simple. Anyone can very quickly understand it and conclude that it is correct. That's good code.

Answer 3

No recursive CTE, just using one to grab numbers sequence from system views. Also cross joined to make sure it works for high numbers. 11k rows in sys.columns on a fresh system.

DECLARE @i INT = 1, @max INT = 100, @a INT = 3, @ax CHAR(4) =  'Fizz', @b INT = 5, @bx CHAR(4) = 'Buzz';
WITH NCTE AS (SELECT TOP (@max) ROW_NUMBER() OVER (ORDER BY t1.Object_id) as n FROM master.sys.columns t1 CROSS JOIN master.sys.columns t2 CROSS JOIN master.sys.columns t3) 
SELECT CASE WHEN n % @a = 0 AND n % @b = 0 THEN @ax+@bx
            WHEN n % @a = 0 THEN @ax
            WHEN n % @b = 0 THEN @bx
            ELSE CAST(n AS VARCHAR)
            END
FROM NCTE;
GO