Changing GUID Primary Keys to Integer Primary Keys

Question

I took over an application a few months ago that used Guids for primary keys on the main tables. We've been having some index related database problems lately and I've just been reading up on the use of Guids as primary keys and I've learnt how much of a bad idea they can be, and I thought it might pay to look into changing them before the database gets too large.

I'm wondering if there's an easy way to change them to Ints? Is there some amazing software that will do this for me? Or is it all up to me?

I was thinking of just adding an extra Int column too all appropriate tables, write some code to poplate this column with 1 - n based on the CreationDate column, writing some more code to populate columns in all related tables, then switching the relationships to the new int columns. Does't sound TOO difficult... Would this be the best way to do it?

Why can't you just do something based on http://stackoverflow.com/questions/3698793/how-to-add-a-new-identity-column-to-a-table-in-sql-server — Angus Connell, Oct 18 '13 at 10:44
Check http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661 and http://stackoverflow.com/questions/17040124/add-an-identity-column-to-existing-table-as-the-primary-key-and-change-order — Nenad Zivkovic, Oct 18 '13 at 10:55
What I did was a multi-step process: (1) find all foreign key relationships and store the SQL commands needed to re-create them, then (2) drop all foreign key constraints, (3) drop all primary keys on the affected tables, (4) create the new `ID INT` columns on those tables, making them the clustered primary key on that table, and (5) restore the foreign key constraints. This can all be scripted pretty easily — marc_s, Oct 18 '13 at 10:56

score 2 · Accepted Answer · answered Oct 21 '13 at 14:23

After combining pieces from all the above links, I came up with this script, simplified for the sake of the answer.

Tables Before Changes

JOB
Id Guid PK
Name nvarchar
CreationDate datetime

REPORT
Id Guid PK
JobId int
Name nvarchar
CreationDate datetime

SCRIPT

-- Create new Job table with new Id column
select JobId = IDENTITY(INT, 1, 1), Job.*
into Job2
from Job
order by CreationDate


-- Add new JobId column to Report
alter table Report add JobId2 int

-- Populate new JobId column
update Report
set Report.JobId2 = Job2.JobId
from Job2
where Report.JobId = Job2.Id

-- Delete Old Id
ALTER TABLE Job2 DROP COLUMN Id

-- Delete Relationships
ALTER TABLE Report DROP CONSTRAINT [FK_Report_Job]
ALTER TABLE Job DROP CONSTRAINT PK_Job

-- Create Relationships
ALTER TABLE [dbo].[Job2] ADD  CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
([JobId] ASC) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

ALTER TABLE [dbo].[Report]  WITH CHECK ADD  CONSTRAINT [FK_Report_Job] FOREIGN KEY([JobId2])
REFERENCES [dbo].[Job2] ([JobId])
ON DELETE CASCADE
ALTER TABLE [dbo].[Report] CHECK CONSTRAINT [FK_Report_Job]


-- Rename Columns
sp_RENAME 'Report.JobId', 'OldJobId' , 'COLUMN'
sp_RENAME 'Report.JobId2', 'JobId' , 'COLUMN'

-- Rename Tables
sp_rename Job, Job_Old
sp_rename Job2, Job

I created the Job2 table because it means I didn't have to touch the original Job table (apart from deleting the relationships), so that everything could easily be put back to it's original state in case something went bad.

Changing GUID Primary Keys to Integer Primary Keys

1 Answers1

Linked