While I attempting to try to convert the nonPartitioning table to partition table, i faced the below issues.
Steps I made.
Assume i have two column in my table one as ID,Date I want to be split the table by Date wise using the column "ID". The ID is the primary key in the table and dozens of child table referencing to this ID,
1. In this case. I Dropped the clustered index and make the new partition key for column "ID" and partitioned the table successfully. I have used the below queries for partitioning.
a. Create Clustered Index PK_ID on TABLEA(ID) on SCHEME_YR(dt)
b. Drop index PK_ID on TABLEA with (online = OFF, Move To SCHEME_YR (dt))
The table got partitioned successfully but, i have the issue while recreating the primary key clustered index for the column "ID"
I got the error message while i create the index
ALTER TABLE mxDocument WITH NOCHECK
ADD CONSTRAINT PK_mxDocument1 PRIMARY KEY CLUSTERED ([lSysKey]) on SArch_Yr (dtCompletion)
Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'mxDocument'. Drop the existing clustered index 'PK_mxDocument' before creating another.
I shouldn't include/add the other column to make a primary key. because the Column ID id the referencing column for other child tables.
Kindly help me to resolve this issue..
thanks & regards