What is the fastest way to convert all PK and FK GUID Columns and relationships to int?

Question

I'm currently developing an ASP.NET MVC application with a ton of PK-FK relationships in the database. In the beginning of development, the team I WAS working with voted against my recommendation to use INTs for all PKs... they decided to use GUIDs.

Long story long... the team has split ways, and now I have control to change things... I switched from WebForms to MVC and I would like to convert all of the Guids to Ints... I hate the ugly URL's like "Http://MyApp.com/Controller/Action/13cd6abc-8555-498a-ad7d-f060aace6d76 YUCK!! Plus the overhead involved in indexing guids... plus it probably doesn't help my Linq to Entities model.

Using SQLServer 2008, ASP.NET MVC 2, Linq to Entities (Entity Framework)

Anyone know a quick way to convert those pesky guids into ints?

Answer 1

1. Script the generation of all primary and foreign keys
2. Drop all primary and foreign keys
3. Raname all GUID columns from SomethingID to SomethingGUID
4. Create new int SomethingID columns
5. Recreate primary keys
6. Write scripts to update all foreign keys using GUID relationships
7. Recreate foreign keys
8. Drop GUID columns

Answer 2

I assume you also want the INTs to be IDENTITYs. Here is how to migrate the data:

1. Put application offline, no DB updates can occur, suspend all SQL Agent jobs, imports etc
2. For each PK:
   • create a copy table with same structure + 1 new int identity(1,1) column
   • copy data from old table to new table
   • use sp_rename to swap the tables so that the new table becomes the old table
   • Rinse, cycle and repeat for all tables with PKs (tables now have both GUIDs and INTs)
3. For each FK:
   • add a new INT column on the FK source table (the one that has the constraint)
   • run an update that joins the two tables on the GUIDs and assigns the new int PKs to the relation column
4. Add back FK constaints, now on the INT columns
5. Drop GUID columns