T-SQL: When is dropping and recreating tables needed when changing the primary key type?

Question

I have the following tables:

CREATE TABLE B( Id  [uniqueidentifier] PRIMARY KEY NOT NULL,    [Name] varchar(100) NULL;
CREATE TABLE A (Id  [uniqueidentifier] PRIMARY KEY NOT NULL,    BId [uniqueidentifier] NOT NULL CONSTRAINT FK_A_B   FOREIGN KEY(BId)    REFERENCES dbo.B(Id));

This is a simplified example, actually there are other columns in the tables that are irrelevant to the question. I need to change both tables' primary key columns to be of type int and to auto-increment BUT to also preserve the old Id values as GUID columns My question is: Which is the best way to do these changes? Two ways come to mind - the first is to drop all indexes on B.Id, add a new identity column to B table, do the same for A.Id, then add a new null column to A (BId_New INT), update A and then make BId_New not null. The other way is to save the tables' data to copied tables, truncate the A and B tables, then alter their schema and insert the values from the copied tables to the actual ones. Which way is better and under what circumstances?

Answer 1

1. Create the new Identity key on B table, you can define it as Not Null to start with since the identity attribute will populate it.
2. Remove the FK constraint on table A
3. Remove the PK constraint on table B. You may also need to drop the clustered index if you want your new PK to be a clustered index as well. I'm not sure if removing the PK constraint will also remove the clustered index.
4. Create a new PK constraint on the identity attribute
5. Create the new int attribute in the A table. DO NOT use identity.
6. Update the new int attribute by joining A & B on the GUID and retrieving the new B primary key
7. Alter the new column in table A to change it to Not Null
8. Create the FK constraint to link table A to table B on the new int key