當ClusterIndex有Partition時,PK(Noncluster)有什麼要求,才能正確的Partition Switch?- SQL Server

Posted by: bart30508 | in SQL Server, database | 8 months, 3 weeks ago |

答:

如果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.com
https://dotblogs.com.tw/rockchang/2017/08/11/113423

Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

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)

Authors

bart30508 (146)

Feeds

RSS / Atom

當ClusterIndex有Partition時,PK(Noncluster)有什麼要求,才能正確的Partition Switch?- SQL Server

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.