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?