What do you expect the result of the following query to be? No cheating, don’t run until you’ve answered!
WITH DATA (Numbers) AS (SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION SELECT 3) SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA WHERE Numbers IS NULL
The final UNION negates the duplicates selected with the previous UNION ALL statements. The COUNT(ALL expression) evaluates the expression for each row in a group and returns the number of nonnull values. ALL Applies the aggregate function to all values. ALL is the default.For investigation, change the final UNION to UNION ALL, and the SELECT COUNT (ALL Numbers) to COUNT (*)
Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.