t-sql 2012 check for bit value = 1

Question

t-sql 2012 check for bit value = 1

wendy elizabeth

SSCoach

Points: 16915
More actions
December 7, 2016 at 10:10 am

#315784

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?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

wendy elizabeth (12/7/2016)
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?

Rather than your suggested version,

where test1 = cast(1 as bit)

is preferable, as it avoids having to cast every value in the column.

Check for NULL values if you are interested in returning them, otherwise there is no need.

If your query is still running too slowly, you may need to consider adding one or more indexes to support the queries you need to run.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.