beware BIT columns in SQL2000

found a strange one today… if you have a BIT column and you write the following TSQL:

WHERE BitColumnName = 1

SQL Server doesn't quite do what you want it to do. rather than converting the 1 to a bit and then applying the where clause filter, it instead converts BitColumnName to an int and then applies the filter. this is problematic since you won’t be properly using indexes you have on the bit column. to avoid this, write:

WHERE BitColumnName = convert(bit, 1)

this is fixed in SQL2005--