Approach for altering Primary Key from GUID to BigInt in SQL Server related tables
Posted
by Tom
on Stack Overflow
See other posts from Stack Overflow
or by Tom
Published on 2010-04-28T14:24:19Z
Indexed on
2010/04/28
18:17 UTC
Read the original article
Hit count: 252
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
© Stack Overflow or respective owner