FIX: SELECT with WHERE Clause from UNION of SELECT Statements Uses Table Scan (218455)

SYMPTOMS

If a view contains a UNION of two or more SELECT statements, and you SELECT from the view using a WHERE condition, a table scan will be used for each of the tables in the view, even though it is more efficient to use indexes.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

MORE INFORMATION

To reproduce this problem, run the following Transact-SQL script:

use pubs
go

drop table t1, t2
go
drop view union_view
go

create table t1 (col1 int, col2 char(255), col3 char(255), col4 char(255))
go
create table t2 (col1 int, col2 char(255), col3 char(255), col4 char(255))
go

set nocount on
go

-- populate the tables with some data
declare @c int
select @c = 1
while (@c <= 500)
begin
  insert t1 values (@c, 'a', 'b', 'c')
  insert t2 values (@c, 'a', 'b', 'c')
  select @c = @c + 1
end
go

create unique index idx1 on t1 (col1)
create unique index idx2 on t2 (col1)
go

create view union_view
as
select * from t1
union
select * from t2
go

set showplan on
go

print 'No view -- Indexes used'
go
select * from t1 where col1 = 75
union
select * from t2 where col1 = 75
go

print 'Using a view -- Indexes NOT used'
go

select * from union_view where col1 = 75
go