Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
How many rows do you have in this table? –  Magnus Sep 5 '12 at 22:27
    
@Magnus: it's about 100 tables, not one. –  ypercube Sep 5 '12 at 22:55
add comment

migrated from stackoverflow.com Sep 5 '12 at 22:17

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.