Jeff Moden’s Tally Table Function (Generate Large Numbers)

Testing Jeff’s excellent number table generator with the examples below takes ~8 to ~13 minutes on this 6-CPU 8GB ram, 3.8 GHz virtual machine running SQL Server 2019 Developer Edition CU8. Server maxdop is set to 6, parallelism cost threshold is set to 50. Database maxdop is also set to 6.

Disclaimer: This virtual machine is not optimized for performance. The 6 equally sized tempdb data files are purposely located on slow storage (only ~6200 IOPS) through the same storage controller in an attempt to exaggerate the effect of any “bad” parts of queries. There are also backup (and other various) jobs possibly running during these tests.


Test Query 1: Select Into #temp version, 1 billion numbers.

drop table if exists #temp;
select [N] into #temp from [dbo].[fnTallyBig]( 1073741824 );

Result: Test query 1 took ~13 minutes, went parallel and used all 6 cores; hovered around 15% CPU usage – according to the Hyper-V Manager.


Test Query 2: Select @n=N version, 1 billion numbers.

declare @n bigint;
select @n=[N] from [dbo].[fnTallyBig]( 1073741824 );

Result: Test query 2 took ~8 minutes and also went parallel and used all 6 cores; hovered around 9% CPU usage – according to the Hyper-V Manager.


Testing conclusion: There are no known bad parts of Jeff Moden’s script. (Yay!)
Also: The tempdb performance on this virtual machine is horrible! 😉


This is the modified version of Jeff Moden’s “fnTally” script to create the number generating function. I’ve removed the @ZeroOrOne parameter in favor of always starting at zero.

If you read through the comments, there are alternate versions.. One that lets you specify the starting number!

CREATE OR ALTER FUNCTION [dbo].[fnTallyBig]( @MaxN BIGINT )
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from 0 up to and including @MaxN with a max value of 10 Quadrillion.

 Usage:
--===== Syntax example
 SELECT t.[N]
   FROM [dbo].[fnTallyBig](@MaxN) t;
 
 select t.[N] into #numbers from [dbo].[fnTallyBig](4294967296) t;

 @MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.

 Please see the following notes for other important information.

Original script can be found at https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

 Jeff's Notes:
 1. This code works for SQL Server 2008 and up.
 2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URL for how it works.
    https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
 3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertible to the BIT data-type will cause the sequence to start at 1.
 4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 4,294,967,296. If a larger number is used, the function will silently truncate after that max. If you actually need a sequence with that many or more values, you should consider using a different tool. 😉
 7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending sort is required, use code similar to the following. Performance will decrease by about 27% but it's still very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally2(@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is implicitly sorted by ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
 9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
    This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
    This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12(mi:ss).

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 07 Sep 2013 - Jeff Moden 
        - Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment. 
          This will also make it much more difficult for someone to actually get silent truncation in the future.
 Rev 04 - 04 Aug 2019 - Jeff Moden
        - Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES clause, this code is "only" compatible with SQLServer 2008 and above.
        - Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they changed the name of the company (twice, actually).
        - Update the flower box notes with the other changes.
**********************************************************************************************************************/
      
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ) V(N))           --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8;

RE: How to run your CTE just once, and re-use the output

There’s an excellent article “How to run your CTE just once, and re-use the output” over at sqlsunday.com, but a few people on Reddit are wondering if inserting into a #temp table or @table variable would be any better [for performance].

As Brent Ozar is fond [paraphrasing!] of saying, “If you don’t know, you’re wasting your time tuning!“.

Spinning up a 4GHz, 6 CPU Hyper-V with 8GB ram, SQL Server 2019 Developer Edition, parallelism cost threshold to 50, server maxdop to 6, attaching a copy of the Stack Overflow March 2016 database onto a 7200RPM 1TB hard drive, setting compatibility level to 190, database maxdop to 6, creating the index given on sqlsunday.com (which took 9 minutes), setting “set statistics io on;” and running each query, here are the results I see on this virtual test server.


Query 1, “CTE + Union All”
(8926714 rows affected)

Table 'Users'. Scan count 28, logical reads 322600.
Table 'Posts'. Scan count 30, logical reads 333030.

Total logical reads: 655,630. This is our baseline.


Query 1-B, “Query 1 + Suggested Index”

Query 1 is suggesting to create an index, in addition to the index already used from the blog post.

CREATE NONCLUSTERED INDEX ix_suggested_index
ON [dbo].[Users] ([Reputation])
INCLUDE ([DisplayName]);

This took 4 seconds to create. Let’s see if Query 1 runs any better with this new suggested index. (The suggested index is now used 4 times alongside the previous index.)

(8926714 rows affected)

Table 'Users'. Scan count 28, logical reads 99860.
Table 'Posts'. Scan count 30, logical reads 333030.

Logical reads on the Users table has dropped significantly, and the query returned results sooner.

Total logical reads: 432,890. (Better than Query 1.)


Query 2, “Query 1 – Expanded”
(8218141 rows affected)

Table 'Users'. Scan count 28, logical reads 323384.
Table 'Posts'. Scan count 28, logical reads 333030.

Already, we can see that the row counts are different. Query 1 is returning 708,573 more rows than Query 2. Without spending more time digging into why, my guess would be the query was incorrectly expanded [from the cte to subqueries] to the post?

Total logical reads: 656,414. (Worse. And the row count is new lower.)


And again, now with the suggested index. (Same query as Query 2, just with index.)

(8218141 rows affected)

Table 'Users'. Scan count 28, logical reads 99924.
Table 'Posts'. Scan count 29, logical reads 332248.

Logical reads on the Users table has dropped significantly, and this query also returned results sooner.

Total logical reads: 432,172. (Slightly better than the baseline. But I do not trust the results as the row count is still lower.)


Query 3, “CTE + Cross Apply + Union All”
(8926714 rows affected)

Table 'Posts'. Scan count 10, logical reads 109998.
Table 'Users'. Scan count 7, logical reads 24996.

Warning: Null value is eliminated by an aggregate or other SET operation.

Same row count, fewer logical reads on both tables. Except now we have a null-warning!

I would prefer to rewrite all versions of the query to be properly sorted and then run a compare using WinMerge on the resulting text files.

Total logical reads: 134,994. (A lot better than the baseline!)


Query 4, “CTE + Cross Apply”
(8926714 rows affected)

Table 'Posts'. Scan count 10, logical reads 110212.
Table 'Users'. Scan count 7, logical reads 24996.

It did run a few seconds faster than Query 3 with about the same logical reads. The execution plan is also much cleaner. Remember: these tests are still being ran with the SQL Server 2019 suggested index created after the original Query 1 on the Users table.

Total logical reads: 135,208. (A lot better than baseline!)


I’ll update this post with more information about using #temp tables and @table variables when I have the time (and ambition). For now, I have to get back to “working” 😉.


…Later the next day…

Query 5, “Select into #temp + Cross Apply Values”
SELECT u.DisplayName, u.Reputation,
        SUM(p.ViewCount) AS ViewCount,
        SUM(p.CommentCount) AS CommentCount,
        SUM(p.FavoriteCount) AS FavoriteCount
into #temp
FROM dbo.Users AS u
LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
GROUP BY u.DisplayName, u.Reputation;

SELECT DisplayName, x.Metric, x.[Value]
FROM #temp cte
CROSS APPLY (
    VALUES ('Reputation', cte.Reputation),    --- 1
           ('Views',      cte.ViewCount),     --- 2
           ('Comments',   cte.CommentCount),  --- 3
           ('Favorited',  cte.FavoriteCount)  --- 4
    ) AS x(Metric, [Value])
WHERE x.[Value]>0;

And the results of selecting into a #temp table, and then selecting from that #temp table (otherwise the same query as Query 4).

(4569697 rows affected)

Table 'Posts'. Scan count 9, logical reads 110144.
Table 'Users'. Scan count 7, logical reads 24940.
Warning: Null value is eliminated by an aggregate or other SET operation.

(8926714 rows affected)

Table '#temp'. Scan count 7, logical reads 28206.

Total logical reads: 163,290. (Better than baseline, but more than Query 4. Plus took ~20 seconds longer to return results.)


Query 6, “insert @table then cross apply”
declare @temp table(
	DisplayName nvarchar(40),
	Reputation int,
	ViewCount int,
	CommentCount int,
	FavoriteCount int
)

insert into @temp( DisplayName, Reputation, ViewCount, CommentCount, FavoriteCount )
SELECT u.DisplayName, u.Reputation,
        SUM(p.ViewCount) AS ViewCount,
        SUM(p.CommentCount) AS CommentCount,
        SUM(p.FavoriteCount) AS FavoriteCount
FROM dbo.Users AS u
LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
GROUP BY u.DisplayName, u.Reputation;

SELECT DisplayName, x.Metric, x.[Value]
FROM @temp cte
CROSS APPLY (
    VALUES ('Reputation', cte.Reputation),    --- 1
           ('Views',      cte.ViewCount),     --- 2
           ('Comments',   cte.CommentCount),  --- 3
           ('Favorited',  cte.FavoriteCount)  --- 4
    ) AS x(Metric, [Value])
WHERE x.[Value]>0;

And the results of this @table query.

(4569697 rows affected)

Table 'Posts'. Scan count 2, logical reads 109728.
Table 'Users'. Scan count 1, logical reads 24782.
Warning: Null value is eliminated by an aggregate or other SET operation.

(8926714 rows affected)

Table '#B63020B0'. Scan count 7, logical reads 28203.

Total logical reads: 162,713. (Slightly better than Query 5, but still 27,505 more than Query 4. That’s an extra 225 MB in writes+reads and a slower query!)


Conclusion

The article “How to run your CTE just once, and re-use the output” over at sqlsunday.com had the correct idea.

Using #temp tables when you need to use the same data in multiple separate queries is usually a good idea (better than repeatedly querying the same source tables), but if the same data needs to be queried multiple times in the same query, then go with the Query 4’s “CTE+cross apply” method.

Why the @table variables sucked: As far as I’ve read, we don’t get up-to-date statistics when using @table variables. And that would cause less than ideal query plans. If I’m behind on my readings about this, please let me know with a link to some updated reading material!

Easy Pattern to implement IDisposable

Here is my C# implementation for an easy way to implement the IDisposable pattern in your classes.

Just override DisposeManaged() and/or DisposeNative() when needed.

The embedded code shown below is pulled directly from my GitHub gist here.
If any formatting is ‘off’ or links don’t work, contact the WordPress.com complaint department. :/

// Copyright © Protiguous. All Rights Reserved.
//
// This entire copyright notice and license must be retained and must be kept visible in any binaries, libraries, repositories, or source code (directly or derived) from our binaries, libraries, projects, solutions, or applications.
//
// All source code belongs to Protiguous@Protiguous.com unless otherwise specified or the original license has been overwritten by formatting. (We try to avoid it from happening, but it does accidentally happen.)
//
// Any unmodified portions of source code gleaned from other sources still retain their original license and our thanks goes to those Authors.
// If you find your code unattributed in this source code, please let us know so we can properly attribute you and include the proper license and/or copyright(s).
//
// If you want to use any of our code in a commercial project, you must contact Protiguous@Protiguous.com for permission, license, and a quote.
//
// Donations, payments, and royalties are accepted via bitcoin:1Mad8TxTqxKnMiHuZxArFvX8BuFEB9nqX2 and PayPal:Protiguous@Protiguous.com
//
// ====================================================================
// Disclaimer: Usage of the source code or binaries is AS-IS.
// No warranties are expressed, implied, or given.
// We are NOT responsible for Anything You Do With Our Code.
// We are NOT responsible for Anything You Do With Our Executables.
// We are NOT responsible for Anything You Do With Your Computer.
// ====================================================================
//
// Contact us by email if you have any questions, helpful criticism, or if you would like to use our code in your project(s).
// For business inquiries, please contact me at Protiguous@Protiguous.com.
//
// Our software can be found at "https://Protiguous.Software/"
// Our GitHub address is "https://github.com/Protiguous".
//
// File "ABetterClassDispose.cs" last formatted on 2020-11-07.
#nullable enable
namespace Librainian.Utilities {
using System;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using System.Threading;
/// <summary>
/// <para>A class for easier implementation the proper <see cref="IDisposable" /> pattern.</para>
/// <para>Implement overrides on <see cref="DisposeManaged" />, and <see cref="DisposeNative" /> as needed.</para>
/// <code></code>
/// </summary>
/// <remarks>ABCD (hehe).</remarks>
/// <copyright>Created by Protiguous.</copyright>
public abstract class ABetterClassDispose : IDisposable {
private Int32 _hasDisposedManaged;
private Int32 _hasDisposedNative;
private Int32 _hasSuppressedFinalize;
public Boolean HasDisposedManaged {
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
get => Interlocked.CompareExchange( ref this._hasDisposedManaged, 0, 0 ) == 1;
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
set {
if ( this.HasDisposedManaged ) {
return; //don't allow the setting to be changed once it has been set.
}
Interlocked.Exchange( ref this._hasDisposedManaged, value ? 1 : 0 );
}
}
public Boolean HasDisposedNative {
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
get => Interlocked.CompareExchange( ref this._hasDisposedNative, 0, 0 ) == 1;
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
set {
if ( this.HasDisposedNative ) {
return; //don't allow the setting to be changed once it has been set.
}
Interlocked.Exchange( ref this._hasDisposedNative, value ? 1 : 0 );
}
}
public Boolean HasSuppressedFinalize {
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
get => Interlocked.CompareExchange( ref this._hasSuppressedFinalize, 0, 0 ) == 1;
[MethodImpl( MethodImplOptions.AggressiveInlining )]
[DebuggerStepThrough]
set {
if ( this.HasSuppressedFinalize ) {
return; //don't allow the setting to be changed once it has been set.
}
Interlocked.Exchange( ref this._hasSuppressedFinalize, value ? 1 : 0 );
}
}
/// <summary>Can be changed to a property, if desired.</summary>
/// <returns></returns>
public Boolean IsDisposed => this.HasDisposedManaged && this.HasDisposedNative;
/// <summary>
/// <para>
/// Disposes of managed resources, then unmanaged resources, and then calls <see cref="GC.SuppressFinalize" /> for this object.
/// </para>
/// <para>Note: Calling <see cref="Dispose()" /> multiple times has no effect beyond the first call.</para>
/// </summary>
[DebuggerStepThrough]
public void Dispose() {
if ( !this.HasDisposedManaged ) {
try {
this.DisposeManaged(); //Any derived class should have overloaded this method and disposed of any managed objects inside.
}
catch ( Exception exception ) {
Debug.WriteLine( exception );
}
finally {
this.HasDisposedManaged = true;
}
}
if ( !this.HasDisposedNative ) {
try {
this.DisposeNative(); //Any derived class should overload this method.
}
catch ( Exception exception ) {
Debug.WriteLine( exception );
}
finally {
this.HasDisposedNative = true;
}
}
if ( this.IsDisposed && !this.HasSuppressedFinalize ) {
try {
GC.SuppressFinalize( this );
}
catch ( Exception exception ) {
Debug.WriteLine( exception );
}
finally {
this.HasSuppressedFinalize = true;
}
}
}
/// <summary>
/// Just calls <see cref="Dispose()" />. The parameter <paramref name="dispose" /> has no effect with this design.
/// </summary>
/// <param name="dispose"></param>
[DebuggerStepThrough]
public void Dispose( Boolean dispose ) => this.Dispose();
/// <summary>Override this method to dispose of any <see cref="IDisposable" /> managed fields or properties.</summary>
/// <example>
/// <code>using var bob = new DisposableType();</code>
/// </example>
[DebuggerStepThrough]
public virtual void DisposeManaged() { }
/// <summary>
/// Dispose of COM objects, handles, etc in this method.
/// </summary>
[DebuggerStepThrough]
public virtual void DisposeNative() =>
/*make this virtual so it is optional*/
this.HasDisposedNative = true;
/*
/// <summary>Set via <see cref="SetDisposeHint" /> to help find if an object has not been disposed of properly.</summary>
[CanBeNull]
private String? DisposeHint { get; set; }
*/
/*
/// <summary>Call at any time to set a debugging hint as to the creator of this disposable.</summary>
/// <param name="hint"></param>
[Conditional( "DEBUG" )]
public void SetDisposeHint( [CanBeNull] String? hint ) => this.DisposeHint = hint;
*/
}
}
// Copyright © Protiguous. All Rights Reserved.
//
// This entire copyright notice and license must be retained and must be kept visible in any binaries, libraries, repositories, or source code (directly or derived) from our binaries, libraries, projects, solutions, or applications.
//
// All source code belongs to Protiguous@Protiguous.com unless otherwise specified or the original license has been overwritten by formatting. (We try to avoid it from happening, but it does accidentally happen.)
//
// Any unmodified portions of source code gleaned from other sources still retain their original license and our thanks goes to those Authors.
// If you find your code unattributed in this source code, please let us know so we can properly attribute you and include the proper license and/or copyright(s).
//
// If you want to use any of our code in a commercial project, you must contact Protiguous@Protiguous.com for permission, license, and a quote.
//
// Donations, payments, and royalties are accepted via bitcoin:1Mad8TxTqxKnMiHuZxArFvX8BuFEB9nqX2 and PayPal:Protiguous@Protiguous.com
//
// ====================================================================
// Disclaimer: Usage of the source code or binaries is AS-IS.
// No warranties are expressed, implied, or given.
// We are NOT responsible for Anything You Do With Our Code.
// We are NOT responsible for Anything You Do With Our Executables.
// We are NOT responsible for Anything You Do With Your Computer.
// ====================================================================
//
// Contact us by email if you have any questions, helpful criticism, or if you would like to use our code in your project(s).
// For business inquiries, please contact me at Protiguous@Protiguous.com.
//
// Our software can be found at "https://Protiguous.Software/&quot;
// Our GitHub address is "https://github.com/Protiguous&quot;.
//
// File "ExampleUsingABetterClassDispose.cs" last formatted on 2020-11-07.
#nullable enable
namespace Librainian.Tests {
using System;
using System.IO;
using Utilities;
public class ExampleUsingABetterClassDispose : ABetterClassDispose {
private MemoryStream? _memoryStream = new MemoryStream();
private SysComObject? _sysComObject = new SysComObject();
public ExampleUsingABetterClassDispose() => this._sysComObject?.ReserveMemory();
public override void DisposeManaged() {
using ( this._memoryStream ) {
this._memoryStream = null;
}
base.DisposeManaged();
}
public override void DisposeNative() {
this._sysComObject?.ReleaseMemory();
this._sysComObject = null;
base.DisposeNative();
}
}
/// <summary>
/// A fake COM interface object.
/// </summary>
public class SysComObject {
public void ReleaseMemory() {
throw new NotImplementedException( "Not actual code." );
}
public void ReserveMemory() {
throw new NotImplementedException( "Not actual code." );
}
}
}

Driving Around Town

While driving around I pay attention to the way the other drivers behave.

I notice who lets who in, and who cuts who off.

I notice who speeds up to (& sometimes through!) red lights or stop signs.

And you know what the common denominator is among all of these bad drivers? The trump flags, the trump stickers.

Go figure..

ALL, ANY, and SOME: 3 SQL operators you do NOT want to use…

http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

-- 4 questions with BoxOf3Coins
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in BoxOf3Coins?',case when 25 > any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in BoxOf3Coins?',case when 25 > all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in BoxOf3Coins?',case when 25 = any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in BoxOf3Coins?',case when 25 = all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

-- Now answer the same question with the EmptyBox
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in EmptyBox?',case when 25 > any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in EmptyBox?',case when 25 > all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in EmptyBox?',case when 25 = any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in EmptyBox?',case when 25 = all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;