解决分区表归档（switch）失败

错误信息&＃xff1a;

&＃39;ALTER TABLE SWITCH&＃39; 语句失败。表&＃39;dbName.dbo.tableName&＃39; 已分区&＃xff0c;但 索引&＃39;IX_indexName&＃39; 未分区。

网上找了很多都解决不了&＃xff0c;摸索一番总算搞定了。

解决方法&＃xff1a;

先删除这个表上除主键之外的所有索引&＃xff0c; 然后添加索引&＃xff08;基于分区方案上的分区列&＃xff09;

脚本&＃xff1a;

IF EXISTS(SELECT * FROM sys.indexes AS i WHERE i.[object_id]&＃61;OBJECT_ID(&＃39;[dbo].[tableName]&＃39;) AND NAME NOT LIKE &＃39;PK_%&＃39;)
BEGINDECLARE &＃64;sql NVARCHAR(MAX)SET &＃64;sql&＃61;&＃39;&＃39;SELECT &＃64;sql&＃61;&＃64;sql&＃43;&＃39;drop index &＃39;&＃43;NAME&＃43;&＃39; on [dbo].[tableName];&＃39; FROM sys.indexes AS i WHERE i.[object_id]&＃61;OBJECT_ID(&＃39;[dbo].[tableName]&＃39;) AND NAME NOT LIKE &＃39;PK_%&＃39;SET &＃64;sql&＃61;REPLACE(&＃64;sql, &＃39;;&＃39;, &＃39;
&＃39;)PRINT &＃64;sqlEXEC (&＃64;sql)
END
--1.只写一个&＃xff0c;其它的类似
CREATE NONCLUSTERED INDEX [IX_indexName] ON [dbo].[tableName]
([columnName] ASC
) ON [partition_scheme]([partitionColumn]) --分区方案 (分区列)&＃xff0c;其实也就变动了这么一点点