I had bigger plans for this series than what has transpired. I think that is a testament as to how things can rapidly change or other events can change your plans. The first thing that changed my plans for this series was the advent of a competition on ask.sqlservercentral.com centered around the Fizzbuzz question.  Then came along the TSQL Tuesday concerning IO.  My submission for that meme involved some Fizzbuzz examples to help demonstrate the point.  Now the series I had planned was left in a nebulous.  Well this will be an attempt to try and resurrect it and terminate it all with this article.

The IO article helps to fill a gap left from the first article.  In that article I discussed the creation of a numbers table but did not explore the cost of creating that numbers table in order to solve the FizzBuzz question.  If you already have the numbers table then the cost is minimized and spread out across all solutions involved in the use of that numbers table.  However, if you do not have that table then there is significant IO cost in the creation of that table.  That cost increases as the size of the numbers table increases.  As demonstrated by the IO article, the cost for a large numbers table was the eventual filling of my hard drive.  That is certainly not something you would want to be doing on a production system.

So what is an alternative method to this problem while not creating such an IO cost?  My final solution was the result of several trials and tweaking.  Some of them were just for fun, and some were to test performance gains.  I had thought of evaluating the evolution of those trials.  I decided against that and will just go straight to the final two trials.

Version 1

Code block

DECLARE @LIMITBIGINT
,@WhatNum BIGINT
 
SELECT @LIMIT = 100
,@WhatNum = null
;
WITH Nbrs_2( n ) AS (SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 CROSS JOIN Nbrs_2 n3),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT CASE
WHEN n % 15 = 0 THEN 'Dr. Pepper'--Multiples of 3 AND 5
WHEN n % 3 = 0 THEN 'Pepsi'
WHEN n % 5 = 0 THEN 'Coke'
ELSE CAST(n AS VARCHAR(8))
END AS 'FizzBuzz'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY n)
           FROM Nbrs ) D (n)
         WHERE n <= @LIMIT
And (Isnull(@WhatNum,-1) = -1 Or n = @WhatNum)
Go

This solution was labeled as one of my “for fun” tweaks.  However, there was a good reason for this version.  What if I only wanted to know if a specific value was a multiple of 3 or 5 or both?  This gave me the @WhatNum idea to compare and check on that value.  If no number is supplied then I can query the entire range.  In other versions of this same query, I use an abs() function in the where clause on the @Limit variable.  This is another optional tweak, but I feel it should be present (though not demonstrated in the above script).  If I run this script for a specific value the query performs faster than the entire range (as expected).  If I want the entire range returned, then this query performs quite rapidly and is on par with the solutions provided in the editorial comments and the ask answers (ask link provided, editorial link is referenced in the first article in the series).

Version II

Code block

DECLARE @LIMITBIGINT
 
SET @LIMIT = 1000000
;
WITH Nbrs_2( n ) AS (SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 1 UNION all
SELECT 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 CROSS JOIN Nbrs_2 n3),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2)
,D (n) AS (SELECT CASE
WHEN n % 15 = 0 THEN 'Dr. Pepper'--Multiples of 3 AND 5
WHEN n % 3 = 0 THEN 'Pepsi'
WHEN n % 5 = 0 THEN 'Coke'
ELSE CAST(n AS VARCHAR(8))
END AS 'FizzBuzz'
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) B (n)
WHERE n <= ABS(@LIMIT)
 )
SELECT n FROM d

The major speaking points on this version is the final CTE being the Select and FizzBuzz assignment.  In many solutions this is outside of the CTE.  This method performs slightly faster in some cases, and for the most part is on par with the other solutions.

Conclusion

The biggest selling point of both of these solutions is the speed and IO cost.  Neither of them use DISK IO.  That helps speed both of the queries up substantially.

The better thing about this exercise has been the ingenuity that the community has shown.  There are some amazing responses.  Some responses have used pivot tables, others the cascading CTE method, and yet another used a View (which actually may be the fastest).  I learned a great many things from some of the solutions.  Go check out those solutions and discussions - they are worth it.