>>I am considering changing PK Identity Column for a fairly large table (almost 1,000,000 rows) and was wondering how to do it in the most efficient way (without breaking the database). Here is summary:
>>
>>Current situation:
>>Table has a column ORDER_NO INT which is PK and Identity set On. The index on this column is Clustered.
>>
>>New situation:
>>The column ORDER_NO INT will be left in the table, set Identity Off and change the Index to Non-Clustered.
>>Create a new column PK_FIELD and make it PK, set Identity On, Clustered Index. Copy all values (for existing records) from ORDER_NO to PK_FIELD.
>>
>>My understanding is that I have to do it in this order:
>>1. Create the PK_FIELD first, copy values from ORDER_NO.
>>2. Set Identity of ORDER_NO to No
>>3. Set Identity of PK_FIELD to Yes
>>4. Set PK on PK_FIELD
>>5. Change index, making ORDER_NO as Non-clustered and PK_FIELD as Clustered.
>>
>>Will the above work on a 1 million-record table?
>
>The number of records is not a factor.
>
>However, I am not sure you need to create another field. Can you just rename the field at step 2.5?

No, I need to have two fields. Currently the ORDER_NO is both the Order Number for each order and the PK of the table. I will need an ORDER_NO that may have duplicate entries (which PK field does not allow it). And the table has to have a PK field. This is why I need both.
Thank you.