drop table p_test;
drop partition scheme ps_test
drop partition function pf_test
go

create partition function pf_test(date)
as range right
for values ('20101101', '20101102', '20101103')
go


create partition scheme ps_test
as partition pf_test
to ([primary],[primary],[primary],[primary])
go

create table dbo.p_test(dt date null)  
on ps_test(dt) 
go


insert p_test values('20101101');
insert p_test values('20101102');
insert p_test values('20101103');

/*
select * from p_test

2010-11-01
2010-11-02
2010-11-03
*/

--swich

drop table p_switch;
create table p_switch(dt date);
alter table p_switch add constraint ck1
check (dt >= '20101104' and dt is not null);

insert p_switch values('20101104');



alter partition scheme ps_test next used [primary];
alter partition function pf_test() split range('20101104');

alter table dbo.p_switch switch  to dbo.p_test 
partition 5; -- 마지막 파티션번호

/*
--마지막 파티션번호
select max(b.destination_id)
from sys.partition_schemes a
	inner join sys.destination_data_spaces b
		on a.data_space_id = b.partition_scheme_id
	inner join sys.data_spaces c
		on b.data_space_id = c.data_space_id
where a.name = 'ps_test'		
*/