Why does SQL Server 2005 think COUNT(...) is nullable?

Question

When I do

CREATE VIEW Test1 AS
WITH OneRow AS (SELECT a = 1)
SELECT countt = COUNT(*)
FROM OneRow

GO

SELECT COLUMN_NAME, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Test1'

I get

COLUMN_NAME IS_NULLABLE
----------- -----------
     countt         YES

I would have expected IS_NULLABLE to be NO, though, since I thought that COUNT(*) always returns a number.

Are there circumstances in which COUNT(*) could return NULL? Is there any danger wrapping it up as ISNULL(COUNT(*), 0)? It seems odd that that would be necessary. Indeed, is there any scenario in which COUNT(anything) could return NULL?

Answer 1

I don't think your specific case will ever see a NULL in that computed column, and I don't think there's a problem in wrapping the column in ISNULL(). SQL Server typically reports computed columns as NULLABLE.

From https://technet.microsoft.com/en-US/library/ms191250.aspx:

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.