0

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is dropped. Again after the partitioning when I tried creating the primary key (clustered) again, It created without any errors. But to my surprise when I checked the table the partition was removed. Thus on doing more research on this, I found that when we create a new clustered index on a partitioned table the partition goes off.

Can anyone please help me out in creating a clustered primary key on the partitioned table, since non clustered primary key is degrading the performance.

  • 4
    ALL unique constraints (that would include a primary key) involved in a partitioning scenario MUST have the partition column defined as part of the base index definition (subset of the index key) - (not merely an included column). In order to use your existing paritition setup, you'd have to include the datetime as part of the primary key. Alternatively, you could create a completely different partition scheme and partition function to support the primary key, but the clustered and non-clustered indexes would not be aligned preventing you from using switch or truncate. – Scott Hodgin Mar 14 at 11:32
  • @ScottHodgin Since my column used for partitioning allows null value, I can't include this column while creating the primary key. Is there any other way out ? – l.lijith Mar 18 at 14:58
  • I think your only option is to create a separate partition function and scheme for the primary key. However, as I indicated in my first comment - the clustered and non-clustered indexes would not be aligned preventing you from using switch or truncate. – Scott Hodgin Mar 18 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.