答:
如果A表有PK
```
ALTER TABLE [A] SWITCH PARTITION 1 TO [B] PARTITION 1
```
則A的PK也必須要Partiton, 也就是
(1)A表 PK也必須包含Partition的欄位
(2)A的PK並且不能只單純ON在FileGroup上,而是要真的Partition
(3)A的PK的Partition Schema可以與 A的Cluster的Partition Schema不同,也可正確Partition Switch過去 (這點Partition時,是Noncluster Index都是如此)
滿足以上條件就可以成功Partition Switch 否則
‘ALTER TABLE SWITCH’ statement failed. The table ‘A’ is partitioned while index ‘PK_A*’ is not partitioned.*
B表可以不要求要有PK! (這點Partition時,是Noncluster Index都是如此)
但若B表有PK則其PK也要滿足以上條件(1)(2),否則會出現
‘ALTER TABLE SWITCH’ statement failed. The table ‘A’ is partitioned while index ‘PK_B’ is not partitioned.
而若B表有PK,條件(3)其Partition必須保證與A表一一對應(A怎麼用哪個Parttition Schema, B就用哪個) (這點Partition時,是Noncluster Index都是如此)
ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘A’ for the index ‘PK_B’ in target table ‘B’ .
參考來源:
ALTER TABLE (Transact-SQL) - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics…docs.microsoft.comhttps://dotblogs.com.tw/rockchang/2017/08/11/113423
SQL ServerSQL Server Analytics Service 1
SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)
Max Chen (159)