LISTING 1: SQL Statement to Access Appropriate View

create table trade_01 (
	tradeid	uniqueidentifier not null default( newid() ),
	tradedate	datetime	not null
)
go

alter table trade_01 add constraint pk_trade_01 primary key nonclustered ( tradeid )
alter table trade_01 add constraint ck_trade_01 check( month( tradedate ) = 1 and year(tradedate) = 1999 )
go

create table trade_02 (
	tradeid	uniqueidentifier not null default( newid() ),
	tradedate	datetime	not null
)
go

alter table trade_02 add constraint pk_trade_02 primary key nonclustered ( tradeid )
alter table trade_02 add constraint ck_trade_02 check( month( tradedate ) = 2 and year(tradedate) = 1999 )
go

insert trade_01 ( tradedate ) values ( '1 jan 1999' )
insert trade_02 ( tradedate ) values ( '1 feb 1999' )
go

create view v_trades
as
select * from trade_01
union all
select * from trade_02
go
set showplan_text on
go
select *
from v_trades
where tradedate = '1 feb 1999'
go
set showplan_text off

go
/*****
	Both tables are accessed.
******/
alter table trade_01 add partcol int not null default( 1 )
go
alter table trade_01 add constraint ck_trade_01works check( partcol = 1 )
go
alter table trade_02 add partcol int not null default( 2 )
go
alter table trade_02 add constraint ck_trade_02works check( partcol = 2 )
go

alter view v_trades
as
select * from trade_01
union all
select * from trade_02
go
set showplan_text on
go
select *
from v_trades
where partcol = 2
go
set showplan_text off
go
/*****
	Only the correct partition is accessed.

	Essentially, in order for partitioned views to work properly in terms of
	the optimizer not accessing the tables unless it needs to, you cannot
	use a function on the column-check expression.
*****/

drop table trade_01
drop table trade_02
drop view v_trades
go