Comparisons Against Columns of Data Type Bit in SQL Server 2000 and 7.0

Some of my queries behave differently in SQL Server 2000 than in 7.0, and the SQL Server 7.0 behavior seems to be wrong. For example, in SQL Server 7.0, the query

USE PubsSELECT * FROM Authors WHERE contract = -99999

returns rows that seem to be wrong because the bit values for contract can equal only 0 or 1. What's happening?

ASQL Server 7.0 might not handle comparisons against columns of data type bit as you expect. For example, when you run the above query in SQL Server 2000 and 7.0, SQL Server 2000 returns zero rows. But SQL Server 7.0 returns 19 rows—all the rows in which contract = 1, which is logically incorrect. Let's consider what's happening in the query.

The query compares an integer value with a column of data type bit. SQL Server must perform an implicit conversion of these different data types to compare the values. The topic "Data Type Precedence" in SQL Server Books Online (BOL) says SQL Server is supposed to convert bit data-type values to int data-type values for comparison. However, you can verify that SQL Server 7.0 incorrectly converts the int value of -99999 to a bit data type rather than converting the bit column to an int data type. To verify the incorrect conversion, enable Show Execution Plan in Query Analyzer by pressing Ctrl+K or by selecting Show Execution Plan from the Query menu. Then in Query Analyzer, run the sample query that we looked at earlier. Place the cursor over the second step in the Execution Plan that Figure 3 shows. The Argument clause on the filter shows that SQL Server is converting a local parameter for comparison with the contract column. Bit columns can hold a value of only 1 or 0, and SQL Server converts values other than 0 to 1; so SQL Server 7.0 converts the value of -99999 to a value of 1. That means that SQL Server 7.0 incorrectly changes the WHERE clause to WHERE contract = 1.

SQL Server 2000, however, correctly converts the bit column contract to an integer value; in this example, the bit value of 1 is converted to an integer value of 1. SQL Server 2000 doesn't convert the -99999 value incorrectly to a value of 1. The value of contract never equals -99999, so zero rows pass the test that the WHERE clause imposes. You can verify that SQL Server 2000 is converting the bit column by running the query again in Query Analyzer with the Show Execution Plan option from the Query menu enabled. Be aware that behavior for queries such as the example in this Q&A will be different as you move from SQL Server 7.0 to SQL Server 2000.