Converting Database from GUID to Integer Keys

Question

I have a medium sized database (~100 tables) that uses GUIDs as primary keys in all tables.

In one of our most active tables the GUIDs are causing an unnecessary increase in storage requirements. The table is essentially a primary key (GUID), 4 foreign keys (GUIDs) and two datetime fields.

I would like to change the primary key and all foreign keys to integers, thus dropping the table to about a third of it's current size. There isn't a huge amount of data in the system yet, so converting existing data shouldn't be a big issue.

My main question here is, what would be the best approach?

1. Convert all referenced tables (and most of the tables in my database) to using integer keys.

2. Add an additional autoincrement integer identity to each of the reference tables to use as a foreign key, and maintain the GUIDs in those tables for outside reference (use through APIs, etc).

Answer 1

Your plan sounds basically OK to me. I would proceed cautiously though - add a new int column to the tables, populate it with unique values (the ROW_NUMBER() function is handy for this kind of thing) and then change it to be the primary key and check all is ok before considering dropping the GUID cols.

You perhaps should create a set of archive tables containing the GUID and PK for each of the tables that you can refer back to if you needed to.