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?

Answer 1

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 ...