Is there a reason why SQL would chose not to use an existing index on a
table, when the data is being request in a UNION statement?
Here is the type of statement I am using:

Select * from Archive..report where cn = '12345'
UNION
select * from production..report where cn = '12345'

There is a primary key on one field, and cn has an Index on it. This is
a very large table, 85 columns and 13 indexes, with 10 million rows in
production and 20 million in Archive. The Production db is very active
with lots of inserts, updates and selects, but very little deletes.

When I run the execution plan, I am getting an Index Seek in Archive on
CN, but an Index Scan in Production on the PrimaryKey index. If I run
the statements independently, both run an Index Seek.

The Union seems to be causing problems, but I do not understand why or
how I can force the Index Seek on Production.

Could the statistics be causing the use of the Index Scan on the
Primary Key?

Thank you for any info!!

Stephen

How to concatenate strings from a column into a single row (with NULL value)?
Single to Multiple columns
Assistance with SQL Script
SQL 2005: Why does DECLARE varchar(MAX) in Stored Procedure not work?
HOW TO: PIVOT for unknown number of column headings?