Approach for altering Primary Key from GUID to BigInt in SQL Server related tables
- by Tom
I have two tables with 10-20 million rows that have GUID primary keys and at leat 12 tables related via foreign key. The base tables have 10-20 indexes each.
We are moving from GUID to BigInt primary keys. I'm wondering if anyone has any suggestions on an approach. Right now this is the approach I'm pondering:
Drop all indexes and fkeys on all the tables involved.
Add 'NewPrimaryKey' column to each table
Make the key identity on the two base tables
Script the data change "update table x, set NewPrimaryKey = y where OldPrimaryKey = z
Rename the original primarykey to 'oldprimarykey'
Rename the 'NewPrimaryKey' column 'PrimaryKey'
Script back all the indexes and fkeys
Does this seem like a good approach?
Does anyone know of a tool or script that would help with this?
TD: Edited per additional information. See this blog post that addresses an approach when the GUID is the Primary: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12749/Default.aspx