Redheadatabase

About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Wednesday, August 19, 2009

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

Labels: , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home