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 a table partitioned on a date column. The Primary Key for the table is a composite key of this date column and 4 other columns.

We are thinking about dropping the Primary Key constraint from this table and simply creating a new non-clustered index.

Is the partitioning affected if we drop the Primary Key constraint since the partitioning column is part of the Primary Key?

share|improve this question
    
Is the primary key also the clustered index? What do you hope to gain with this change? –  Jon Seigel Sep 14 '12 at 2:30
add comment

1 Answer 1

It shouldn't affect the table's partitioning, as the table was created on the partition scheme with the partitioning column specified.

Each partition has it's own HOBT (Heap or B-Tree). So essentially you have a HOBT for each Date in your table.

If the primary key is also a clustered index, you will be converting each partitions' B-Tree to a Heap. Depending upon the size of the table, moving the data from the B-Tree to the Heap could take a considerable amount of time and resources. It's recommended to drop all nonclustereds before attempting to drop the clustered index, then recreating them after (or disable / rebuild). Here's a detailed explanation from Microsoft:

"When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last."

http://msdn.microsoft.com/en-us/library/ms190691%28v=SQL.90%29.aspx

I won't comment on query performance implications of doing this, as I'm not familiar with how the table is queried.

As with any major change, make sure to try this in the test environment before attempting to make this change.

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.