Changing Primary Keys for Existing Tables Containing Data

Hi Erland,

Apologies if I wasn't completely clear - the sequence number was specific to the prefix, we want the rows to start numbering at 1 and continue regardless of the prefix. Does this alter the approach you recommend?

Also, as to creating a new version of the table my concern would be that key and constraint names appear to be unique to the database, not the particular table. Though if that were the case I could drop all keys and constraints on the existing object before creating the new one?

Apologies if I wasn't completely clear - the sequence number was specific to the prefix, we want the rows to start numbering at 1 and continue regardless of the prefix. Does this alter the approach you recommend?

It wasn't clear to me whether you wanted to keep the current numbers. If you don't care about the old numbers, you can just insert into the new table and SQL Server will take care of the rest.

Also, as to creating a new version of the table my concern would be that key and constraint names appear to be unique to the database, not the particular table. Though if that were the case I could drop all keys and constraints on the existing object before creating the new one?

What I typically do to deal with this is:

ALTER SCHEMA guest TRANSFER dbo.tbl

This renames the table from dbo.tbl to guest.tbl, and thereby all constraints etc are also moved to the guest schema.

Now there are no problems with creating the new version of the table schema in the dbo schema (or whichever schema you have the table in.)

You can use any schema for this purpose. The guest schema is convenient, because it exists in every database for legacy reasons.

Hi Erland,

Never thought of transferring the object to another schema - thanks for that!

Expanding on the original question: if we are going to take this approach it's probably best/most efficient if we do the FK transformations at the same time (same change in data type). I was thinking of adding a RowID column to each guest schema table so that the 'old' tables all have a bigint column which I could then lookup when doing the insert statement(s) to the 'new' tables so that the new tables can use the bigint FK values. Do you have any thoughts/suggestions as to that approach?

Use the output clause to create a mapping table:

CREATE TABLE Mapper (NewIdentCol int NOT NULL PRIMARY KEY,
                     OldKeyCol varchar(100) NOT NULL UNIQUE)
INSERT (...)
OUTPUT inserted.PKCol, inserted.OldPK INTO Mapper (IdentCol, OldKeycol)

This assumes that the old key column remains in the table. If that is not the case, I suggest that you set up the mapping table first and then use SET IDENTITY_INSERT ON to insert the generated values.

By the way, if the tables are big, you should consider to copy the rows in batches. I have a talk on this topic that you can view on YouTube: https://www.youtube.com/watch?v=fCme4dTgCsc. (Big here means at least 10 GB in size.)

Hi Erland,

Thanks so much for your help so far! Have been working on this and so far the results are promising. However I've noticed that the database and tempdb files are growing significantly due to this process, which is to be expected (not a problem at the moment as we are running this in dev environment).

However, once the process is completed and the old objects dropped (we would be doing this in a maintenance window where there would be no user activity), how would we shrink the data files and logs to reclaim storage space, while ensuring our production backups don't get messed up?

Thanks!