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