BUG: Query Optimizer May Select an Inefficient Query Plan for a Query Against a Partitioned View (308643)

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
				

REFERENCES

SQL Server Books Online; topics: "Using Partitioned Views"; "Resolving Distributed Partitioned Views"; "Distributed Partitioned View Recommendations"