Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In the article named “Can Not Switch The Partition On SQL Server“, we mentioned that all indexes in the partitioned table should be aligned according to partition schema in order to switch partition. In the mentioned article, a script appeared as follows during the align process. This script is “create unique nonclustered index” script. For details I suggest you to read the above article.
1 2 3 4 5 6 7 8 | 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 = on, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartSchema]([CarrierTrackingNumber]) GO |
When I run this script I receive the below error.
Msg 1908, Level 16, State 1, Line 5
Column ‘CarrierTrackingNumber’ is partitioning column of the index ‘AK_SalesOrderDetail_rowguid’. Partition columns for a unique index must be a subset of the index key.
The solution of the error is;
If you want to create a unique index in the partitioned table, you must add the partition column to the index.
We’re running the script again by editing it as follows. The CarrierTrackingNumber column is our partition column.
1 2 3 4 5 6 7 8 | USE [AdventureWorks2014_new] GO CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail] ( [rowguid] ASC,CarrierTrackingNumber )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = on, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartSchema]([CarrierTrackingNumber]) GO |
You can find details about how to create Partition in the article “How To Create Partition On SQL Server”.
You can also read the following articles to view Partitioned Tables and details.
“How To Find Partitioned Tables In The Database On SQL Server“,
“How To See Partition Details In Partitioned Table On SQL Server”
Hello, this index will allow you insert duplicate rowguid because unique is key pair [rowguid | CarrierTrackingNumber], so I think it’s not really “unique”