Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. Please advise.

share|improve this question
add comment

1 Answer

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.