Query has where (a = a) or (b = b) - the 2nd part causing index scan. Full example included

I've been trying to figure out why this query (on integers) is using an index scan when both values (the original table, and the table its looking up in) are integers.

The first part is used to fill a table with values.

The second part is the search query that I'm battling with. If you run the execution plan on the query, you will see that even though there is an index on the intcol, and the #temp table is of type int, it still uses an index scan and scans all 298 000 rows.

The problem is the query that I'm using, it has
where
(a = a)
or
(b = b)

When i take out the "or (b = b)" then it does not do an index scan.
But I need the "or (b == b)" to have this complicated query work.

Implicit Conversions that cause Index Scans

```
CREATE TABLE dbo.TestImplicitConverts
(
RowID int NOT NULL IDENTITY (1, 1),
BigIntCol bigint NOT NULL,
BitCol bit NOT NULL,
CharCol char(10) NOT NULL,
DateTimeCol datetime NOT NULL,
DecimalCol decimal(10, 2) NOT NULL ,
FloatCol float(53) NOT NULL ,
IntCol int NOT NULL,
MoneyCol money NOT NULL,
NCharCol nchar(10) NOT NULL,
NumericCol numeric(10, 2) NOT NULL,
NVarchrCol nvarchar(50) NOT NULL,
RealCol real NOT NULL,
SmallDateTimeCol smalldatetime NOT NULL,
SmallIntCol smallint NOT NULL,
SmallMoneyCol smallmoney NOT NULL,
TinyIntCol tinyint NOT NULL,
GUIDCol uniqueidentifier NOT NULL,
VarcharCol varchar(50) NOT NULL,
CONSTRAINT PK_TestImplicitConverts PRIMARY KEY CLUSTERED (RowID)

)
GO

-- Create nonclustered indexes on all columns to test implicit conversion affects

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BigIntCol ON dbo.TestImplicitConverts (BigIntCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BitCol ON dbo.TestImplicitConverts (BitCol);
GO
CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_CharCol ON dbo.TestImplicitConverts (CharCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateTimeCol ON dbo.TestImplicitConverts (DateTimeCol);
GO

CREATE NONCLUSTERED INDE

Your predicates are of the form

WHERE  (A AND NOT B) OR (B AND NOT A)

Testing on SQL Server 2008 with the FORCESEEK hint it cannot produce a plan.

If you rearrange them to

WHERE (A OR B) AND NOT (A AND B)

You can then get a plan with a seek (doesn't need the hint).

SELECT intcol,
       intcol
FROM   TestImplicitConverts
WHERE  ( ( intcol IN (SELECT myint
                      FROM   #temp) )
          OR ( intcol IN (SELECT my2ndint
                          FROM   #temp) ) )
       AND NOT ( intcol IN (SELECT myint
                            FROM   #temp)
                 AND intcol IN (SELECT my2ndint
                                FROM   #temp) )

Or another possibility would be

WITH T
     AS (SELECT DISTINCT ISNULL(t1.myint, t2.my2ndint) AS intcol
         FROM   #temp t1
                FULL OUTER JOIN #temp t2
                  ON t1.myint = t2.my2ndint
         WHERE  t1.myint IS NULL
                 OR t2.my2ndint IS NULL)
SELECT T.intcol,
       T.intcol
FROM   T
       JOIN TestImplicitConverts TIC
         ON TIC.intcol = T.intcol

StackExchange Database Administrators Q#52029, answer score: 5