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

Comments

Popular posts from this blog

Unlocking Microsoft Fabric: A Simple Guide when you only have a personal account.

ETL to ELT journey: Break free your Transformations and discover Happiness and Data Zen

Coding is a rollercoaster of efficiency and eyebrow-raising discoveries.