Unique Non Clustered Column in Partitioned table

Question

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a non-aligned index. There is one more column xyz which is UniqueIdentifier datatype and xyz column also has Unique Non-clustered index.

The problem is most of our queries use the XYZ column to retrieve data as XYZ is guid and unique.

At this point we are not sure what will be the impact of setting XYZ column (uniqueIdentifier) with an Aligned Index strategy vs Non-aligned Index strategy.

Answer 1

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not acceptable, the alternative are to:

• remove the unique constraint from the data model (ie. accept that duplicates can occur)
• keep the non-aligned index, with all switch-in/switch-out issues and performance problems