In an existing sql server 2012 database, there are lots of columns defined as (bit,null). When the value is checked to be = 0, the results are returned past. However when I check is made to see if the value = 1, then the results take forever to return.
For example when a field called test1 is defined as (bit,null) and the check is where test1 = 1, the query takes forever to run.
I am looking for a way to make these type of queries run fast.
When I change the above to 'where cast(test1 as int) = 1' the query executes faster. However, can you tell me if the sql I listed is a good solution? Should I check for null values?
Basically would you show me the 'best' sql to use in this case?
I don't have an explanation why checking for true is slow. If anything, I would have thought that casting to int and then doing the logical comparison would have taken longer. Can you turn on query plan (control-m) and run both ways and see what the difference is?
That said, it is unlikely that you have an index on a bit column, so if casting gives you better performance, I don't see anything wrong with doing that (except for the fact that behavior that you cannot explain comes back to bite you sooner or later).
Interesting. Is there perhaps a filtered index for test1 = 0??
Also, check the query plan and make sure SQL is not already implicitly converting the bit value or explicitly cast the comparison value to a bit, to make sure that bit type is used if applicable, that is:
WHERE test1 = CAST(1 AS bit)
Thus, that is my answer for "best" sql in this case.