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

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

Look back and realize how far you came

The Greatest Reasons to use or not to use a Centralized Data Access Architecture