MS SQL Server, no loops. ;)

WITH Numbers AS (
    SELECT 1 as Number
    SELECT Number+1 FROM Numbers WHERE Number < 100
    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?

  • 1
    \$\begingroup\$ A note about compatibility: this also works as written in SQLite 3. For PostgreSQL compatibility, you need WITH RECURSIVE (I believe this is standard), but SQL Server doesn't seem to like the RECURSIVE keyword. SQLite 3 doesn't care either way. Oracle has its own weird syntax involving START and CONNECT keywords. MySQL (still) doesn't support CTEs. \$\endgroup\$ – Dagg Jul 16 '14 at 3:05
  • \$\begingroup\$ With SQL Server, go above 101 and you get: "The statement terminated. The maximum recursion 100 has been exhausted before statement completion". Add say OPTION (MAXRECURSION 1000) and can go higher until "MAXRECURSION option exceeds the allowed maximum of 32767". Tested in SQL Server 2017 CTP 2.1. What if you need to get to 100k or more ? \$\endgroup\$ – Razvan Zoitanu May 24 '17 at 18:38
  • \$\begingroup\$ 100k or more fizz-buzzers? \$\endgroup\$ – nhgrif May 24 '17 at 20:51

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

    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 (
        FROM R16 AS a CROSS JOIN R16 AS b
        WHEN n % 15 = 0 THEN 'FizzBuzz'
        WHEN n %  3 = 0 THEN 'Fizz'
        WHEN n %  5 = 0 THEN 'Buzz'
       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.

  • 1
    \$\begingroup\$ Use ORDER BY (SELECT NULL) to loose a sort operator from the plan. Even faster. Your case statement mixes Unicode and non-Unicode. Make the string literals N''. \$\endgroup\$ – usr Jul 13 '14 at 10:52

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:

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.

  • \$\begingroup\$ nvarchar(3)? Since in this case the longest possible one would be '100'? \$\endgroup\$ – nhgrif Jul 12 '14 at 21:31
  • 2
    \$\begingroup\$ Why complicate unnecessarily? It's just brittle and redundant. There is no value in choosing a shorter length. A varchar(N) does not take N bytes of storage. Neither on disk nor in memory. It only consumes used space. \$\endgroup\$ – usr Jul 12 '14 at 21:32

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.