Explaining COUNT return value when used without group

COUNT returns the number of items found in a group and by default it includes NULL values and duplicates.

The first two statements returns an error and the second two – NULL:

1/0
1/'A'
1/NULL
NULL/1

But wrapped in COUNT they returns 1 and 0:

SELECT COUNT(1/0);    -- 1
SELECT COUNT(1/'A');  -- 1
SELECT COUNT(1/NULL); -- 0
SELECT COUNT(NULL/1); -- 0

The NULL cases can be explain by the docs as

COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

So, the are evaluated, they returns NULLs and as non null values are only count, we get 0.

I am wondering why the first two returns 1?

Having the values in a table, everything seems normal – error is thrown and no value is returned:

DECLARE @DataSource TABLE
(
    [valueA] INT
   ,[valueB] INT
);

INSERT INTO @DataSource ([valueA],[valueB])
VALUES (1, 0);

SELECT COUNT([valueA]/[valueB])
FROM @DataSource;

(1 row affected) Msg 8134, Level 16, State 1, Line 16 Divide by zero error encountered.

Completion time: 2020-03-22T13:47:44.5512536+02:00

Maybe this 1 row affected is returned as COUNT?

Answer

When constants are specified in the COUNT expression, SQL Server can optimize the query at compile time an avoid evaluating the expression at execution time. The resultant value can never be NULL in the first 2 queries and will always be NULL in the last 2 queries.

Below is a snippets from the SELECT COUNT(1/0) and SELECT COUNT(1/NULL) execution plans:

<ScalarOperator ScalarString="Count(*)">
<Const ConstValue="NULL" />

The “1 row affected” message generated by the client tool (SSMS), reflecting the rowcount returned by the INSERT statement. If undesired, add SET NOCOUNT ON; to the beginning of the script.