Microsoft KB Archive/308643

= BUG: Query Optimizer May Select an Inefficient Query Plan for a Query Against a Partitioned View =

Article ID: 308643

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308643



BUG #: 355952 (SHILOH_BUGS)



SYMPTOMS
If the WHERE clause of a query that you run against a partitioned view uses the BETWEEN operator and the values the BETWEEN operator uses are datetime values represented by strings, the query optimizer must search all of the tables instead of only those that cover the search condition on the partitioning column.



WORKAROUND
To work around this problem, use the CAST function to resolve the datetime values represented by stings to datetime values.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
If you define the CHECK constraints a partition view uses properly, the query optimizer only searches the tables that cover the search condition on the partitioning column.

Steps to Reproduce Behavior
Place the following code in the SQL Server Query Analyzer. The code shows how SQL Server can chose a poor query plan on the preceding symptoms, and how you can resolve the datetime values represented by strings to datetime values: create table Jan2001 ( dt datetime not null check(dt between '1/1/2001' and '1/31/2001'), c2 int not null, c3 varchar(20) not null , primary key (dt,c2)) create table Feb2001 ( dt datetime not null check(dt between '2/1/2001' and '2/28/2001'), c2 int not null, c3 varchar(20) not null, primary key (dt,c2) )

create table Mar2001 ( dt datetime not null check(dt between '3/1/2001' and '3/31/2001'), c2  int not null, c3 varchar(20) not null, primary key (dt,c2) ) go

--Create the horizontally partitioned view.

create view AllTrans as select * from Jan2001 union all select * from Feb2001 union all select * from Mar2001 go set nocount on

--Insert test records.

declare @i int set @i = 1 while @i <75 begin insert AllTrans values(dateadd(day,@i,'1/1/01'),@i,'Test') set @i = @i+1 end go print 'Notice that the plan shows all partitions are involved when using string dates:' print '' go SET SHOWPLAN_TEXT ON go select * from AllTrans where (dt between '2/1/01' and '2/15/01') and c2 =1 go SET SHOWPLAN_TEXT OFF go print 'Now notice that plan will only search the partitions based on the WHERE cause of the SELECT' print 'statement when the CAST function is applied to convert string dates to actual datetime data:' print '' go SET SHOWPLAN_TEXT ON go select * from AllTrans where (dt between CAST('2/1/01' as datetime) and CAST('2/15/01' as datetime)) and c2 =1 go SET SHOWPLAN_TEXT OFF go