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

• Microsoft SQL Server version 6.5

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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 

Additional query words: t-sql tsql transql

Keywords          : SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: May 4, 1999