Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If all the indexes in the partition table are not aligned according to the partition, you will get an error like the following during switch operation.
Msg 7733, Level 16, State 4, Line 44
‘ALTER TABLE SWITCH’ statement failed. The table ‘AdventureWorks2014_new.Sales.SalesOrderDetail’ is partitioned while index ‘AK_SalesOrderDetail_rowguid’ is not partitioned.
You can find details about the Partition Switch in the article titled “Sliding Window-Switch Partition-Split Range-Merge Range“.
To include the indexes into the partition (align operation), right click on the index specified in the error and click properties.
From the Storage section, click on the Partition scheme and select the options as shown below and click on the script to get the script of the changes we made and cancel.
Here is a script like this:
1 2 3 4 5 6 7 8 9 | USE [AdventureWorks2014_new] GO CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail] ( [rowguid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartFunc]([CarrierTrackingNumber]) GO |
As you can see in the script, it re-creates the index on the corresponding partition schema.
You can change OFF to on ON in the scripts “ONLINE = OFF” section.
So that it runs without interruption on your system.
When the script has finished rebuilding the index, the switch operation will be completed successfully.
Of course, other indexes on the table should be aligned according to the partition scheme as well.
You can not do the Primary Key in the same way.
To do this, you need to get the Primary Key’s DROP CREATE Script and add the partitioned column after the keys as shown below.
Then at the end of the script you should write ON partition schema name.
And finally you need to add the partition column in parentheses.
1 2 3 4 5 6 7 8 9 10 11 | ALTER TABLE [Sales].[SalesOrderDetail] ADD CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED ( [SalesOrderID] ASC, [SalesOrderDetailID] ASC, CarrierTrackingNumber )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartSchema]([CarrierTrackingNumber]) |
You can find details about creating partitions in the article titled “How To Create Partition On SQL Server“.
You can also use the following articles to see the partitioned tables and details of partitions.
“How To Find Partitioned Tables In The Database On SQL Server“,
“How To See Partition Details In Partitioned Table On SQL Server”
