Changing partition_id in SQL Server partition tables
When switching partition in or out, partition_ids are changing for both source and destination, even if destination is a regular table. At the beginning I thought that new id's are being issured but it appears that SQL Server simply exchanging ids of source and destination. This can help to get alert that partition has been changed or used in applications that track data movements.
Here is a sample script and result screenshot:
--------------------------------------
CREATE PARTITION FUNCTION [f](datetime)
AS RANGE RIGHT
FOR VALUES ('20091014','20091015')
GO
CREATE PARTITION SCHEME [s]
AS PARTITION [f]
ALL TO ([primary])
GO
create table t (
i int,
d datetime) on s(d)
insert into t
select 1,'20091015 01:01' union all
select 2,'20091015 04:01' union all
select 3,'20091015 05:01'
drop table t1
create table t1 (
i int,
d datetime) on [primary]
select * from sys.partitions where object_name(object_id) IN ( 't' ,'t1') order by object_id,partition_number
alter table t switch partition 3 to t1
select * from sys.partitions where object_name(object_id) IN ( 't' ,'t1') order by object_id,partition_number
Here is a sample script and result screenshot:
--------------------------------------
CREATE PARTITION FUNCTION [f](datetime)
AS RANGE RIGHT
FOR VALUES ('20091014','20091015')
GO
CREATE PARTITION SCHEME [s]
AS PARTITION [f]
ALL TO ([primary])
GO
create table t (
i int,
d datetime) on s(d)
insert into t
select 1,'20091015 01:01' union all
select 2,'20091015 04:01' union all
select 3,'20091015 05:01'
drop table t1
create table t1 (
i int,
d datetime) on [primary]
select * from sys.partitions where object_name(object_id) IN ( 't' ,'t1') order by object_id,partition_number
alter table t switch partition 3 to t1
select * from sys.partitions where object_name(object_id) IN ( 't' ,'t1') order by object_id,partition_number
Comments
Post a Comment