SQL Server 2008 R2 makes CONVERT_IMPLICIT tinyint for a bit

I am running a simple query on SQL Server 2008 R2 for the following table:

Id (int, not null)
Enabled (bit, but not null)

Both columns have separate indexes.

So when I run the following query:

SELECT Id FROM Entities WHERE Enabled = 1

      

The execution plan shows that INDEX_SCAN is executing (it is called by CONVERT_IMPLICIT on the Enabled column)

And when I run another request:

SELECT Id FROM Entities WHERE Enabled = '1'

      

or

SELECT Id FROM Entities WHERE Enabled = 'true'

      

or

SELECT Id FROM Entities WHERE Enabled = CAST(1 AS BIT)

      

The execution plan shows that INDEX_SEEK is done.

How CONVERT_IMPLICIT can affect performance on more complex queries, I want to know what is causing SQL Server to behave this way?

UPD:

If I run

SELECT Id FROM Entities WHERE Enabled = 0

      

and then

SELECT Id FROM Entities WHERE Enabled = 1

      

The execution plan shows INDEX_SEEK. In this case, I think SQL Server has collected some optimization statistics and finally found out that there is no reason for CONVERT_IMPLICIT. But unfortunately, I cannot guarantee that my original request will execute with the opposite value.

I would appreciate any clarification I can get.

+3


source to share


1 answer


Are you using the database in compatibility mode 2000? As I read here , if you use this compatibility mode, you will be using this "problem", but you will not be using the 2005 or 2008 compatibility mode.



So, if you need to use 2000 compatibility mode, you will need to use the "= '1" comparison to avoid CONVERT_IMPLICIT, because the type priority between a bit and an int (or tiny int) makes the "upgrade" bit to int.

+3


source







All Articles