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?
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.
