C# ExecuteScalar() null COUNT vs SELECT

Question

I noticed some odd behavior and hoped one of the experts could explain the difference. My UI requires an image is unique before presenting it to the user for their task. I store checksums in the database and query those for unique values. I noticed that my logic 'flips' depending on whether I use a standard SELECT query vs SELECT COUNT. I've isolated it down to this line of code but I don't understand why.

SELECT record FROM table WHERE checksum = something

//This code works correctly (true / false)
Object result = command.ExecuteScalar();
bool checksumExists = (result == null ? false : true);

//Returns TRUE no matter what
Object result = command.ExecuteScalar();
bool checksumExists = (result == DBNull.value ? false : true);

I changed to the following SQL for performance against a large table and my logic 'flipped'

SELECT COUNT (record) FROM table WHERE checksum = something

//Now this code always returns TRUE
Object result = command.ExecuteScalar();
bool checksumExists = (result == null ? false : true);

//Now this is the solution
Object result = command.ExecuteScalar();
bool checksumExists = (Convert.ToInt32(result) < 1 ? false : true);

Does the COUNT statement mean that it will always return a number, even if no rows are found?

The second example (the one that checks for DBNull) will return false only if a row has been found that matches the where condition but the column _record_ has a null value. — Steve, May 08 '19 at 21:14
From [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017), *"COUNT( * ) returns the number of items in a group. This includes NULL values and duplicates."* — Rufus L, May 08 '19 at 21:17
@RufusL I get that. I was also trying to make it safe to receive an empty record set. Thank you. — JoeBob_OH, May 08 '19 at 21:20

David Browne - Microsoft · Accepted Answer · 2019-05-08T22:17:01.607

Does the COUNT statement mean that it will always return a number, even if no rows are found?

Yes. Zero is a number. and

SELECT COUNT(someCol) c FROM table WHERE 1=2

will always return a single row, single column resultset like:

c
-----------
0

(1 row affected)

COUNT is not the most efficient way to check whether any rows meet a criterion, as it will continue to count them beyond the first.

You can use EXISTS or TOP 1 to generate a query that will stop after finding a single row. EG

   select someMatchesExist = case when exists(select * from table where ...) then 1 else 0 end

or

select top (1) 1 as someMatchesExist  from table where ...

Would this be considered the ‘best way’ to check for no matches in a large table? — JoeBob_OH, May 08 '19 at 21:18

C# ExecuteScalar() null COUNT vs SELECT

1 Answers1