Microsoft KB Archive/218455

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

Article ID: 218455

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q218455



BUG #: 16424 (SQLBUG_65)



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

Additional query words: t-sql tsql transql

Keywords: kbbug kbfix KB218455

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.