oracle mssql交换数据库,Oracle Vs MsSQL 之交换分区

近日，看到网友[yangtingkun]的“EXCHANGE分区导致主键重复”(http://yangtingkun.itpub.net/post/468/526325)，因为MsSQL仅支持RANGE分区，故作了修改，下面先重现一下问题：

SQL> create table t_part (id number, name varchar2(30))

2partition by range (id)

3(partition p1 values less than (10),

4partition p2 values less than (100),

5partition p3 values less than (maxvalue));

SQL> create index idx_t_part on t_part (id) local;

SQL> alter table t_part add primary key (id) using index idx_t_part;

SQL> create table t_exchange as select * from t_part;

SQL> create index idx_t_exchange on t_exchange(id);

SQL> alter table t_exchange add primary key (id) using index idx_t_exchange;

SQL> insert into t_exchange values (1,'P1');

SQL> insert into t_exchange values (10,'P2');

SQL> insert into t_exchange values (30,'P2');

SQL> insert into t_exchange values (100,'P3');

SQL> commit;

SQL> select * from t_exchange;

ID NAME

---------- ------------------------------

1 P1

30 P2

10 P2

100 P3

SQL> alter table t_part exchange partition p2 with table t_exchange including indexes without validation;

Table altered.

SQL> select * from t_part;

ID NAME

---------- ------------------------------

1 P1

30 P2

10 P2

100 P3

SQL> select * from t_part partition (p2);

ID NAME

---------- ------------------------------

1 P1

30 P2

10 P2

100 P3

SQL> insert into t_part values (1,'P1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_part;

ID NAME

---------- ------------------------------

1 P1

1 P1

30 P2

10 P2

100 P3

下面再看下MsSQL：

CREATE PARTITION FUNCTION [Pfn_Number](int) AS RANGE RIGHT

FOR VALUES (10,100)

CREATE PARTITION SCHEME [Psc_Number] AS PARTITION [Pfn_Number]

TO ([Primary], [Primary], [Primary])

create table t_part (id int, name varchar(30)) on [Psc_Number](id)

select * into t_exchange from t_part;

insert into t_exchange values (1,'P1');

insert into t_exchange values (10,'P2');

insert into t_exchange values (30,'P2');

insert into t_exchange values (100,'P3');

select * from t_exchange

idname

----------- ------------------------------

1P1

10P2

30P2

100P3

alter table t_exchange switch to t_part partition 2

消息4982，级别16，状态1，第1行

ALTER TABLE SWITCH语句失败。源表'demo.dbo.t_exchange'的检查约束所允许的值对于目标表'demo.dbo.t_part'中分区2定义的范围是不允许的。

可见，在MsSQL中，并不是通过检查具体数据，而是通过CHECK约束条件来实现普通表和表分区之间的快速数据交换。