FIX: Select Query That Compares a Tinyint to a Converted Int in the Where Clause Fails to Find Valid Row (308810)

SYMPTOMS

A query that meets all the following criteria may fail to return the correct result:

The query contains an equality comparison between a tinyint column and an explicit conversion of the constant value zero (0).
The tinyint column has a unique constraint.
The tinyint column has a check constraint with zero (0) defined as the lower bound.

For example:

USE pubs
GO

CREATE TABLE T1(c1 TINYINT)

ALTER TABLE T1 ADD UNIQUE (c1)

ALTER TABLE T1 ADD CHECK(c1 BETWEEN 0 AND 100)

INSERT INTO T1 VALUES(0)
GO

SELECT * FROM T1 WHERE c1 = CAST(0 AS INT)
GO

DROP TABLE T1
GO

The preceding SELECT query should return one row, but it fails to return the row.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem, set the database compatibility level to 70.

NOTE: If you set the compatibility level to 70, you cannot take advantage of many of the new features included in SQL Server 2000.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.