Tuesday , March 19 2024

How To Create Unique Index On Partitioned Tables in SQL Server

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.

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.

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

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. Hello, this index will allow you insert duplicate rowguid because unique is key pair [rowguid | CarrierTrackingNumber], so I think it’s not really “unique”

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories