Would there be any issues if i change the partition column of an existing Partitioned Table to a different column, by dropping the clustered index with "WITH (DROP_EXISTING = ON)" or "WITH (MOVE TO ...)" ?
I tried like below and it changed the partition column to "DateKey2".
CREATE CLUSTERED INDEX CI ON Table1 (DateKey2)
WITH ( DROP_EXISTING = ON )
ON ps_DateKey_Archive(DateKey2)
However, the NonClustered Indexes were still pointing to the ps_DateKey_Archive(DateKey1) and i had to drop & recreate them.
For the Heap, I had to create a Clustered index and then drop it and then rebuild the nonclustered indexes.
Would this create any issues to the existing data in the table or affect performance during insert / querying? Thanks in advance.
----------------------------------------------------------------------------------------------------