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?
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 ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With