sql server change PK type from int to uniqueidentifier

Question

I need to change the type of my primary key column on a table from int to guid. The database already has data that I don't want to lose, and there are foreign keys to consider. Is there a painless way to do this or do I have to manually do it through a big a** script?:) I would appreciate any suggestions

Answer 1

You'll have to do it the hard way, using scripts:

0) get in single user mode
1) add the new GUID column to the main table, and populate it.
2) add the new FK column to each child table and populate them with an UPDATE FROM

UPDATE c
    SET FKcolumn=p.NewGuid
    FROM ChildTable             c
        INNER JOIN ParentTable  p ON p.OldIntID=c.OldIntId

3) drop the existing int FKs
4) drop the old int columns
5) add the new FKs on the guid column
6) get out of single user mode

you should be able to get SQL Server Management studio to generate the scripts for adding and dropping the columns and keys. Just make the changes in SSMS and click on the "Generate Change Script" toolbar icon and you can cut and paste the code to a text file.

Answer 2

1. Use option "Generate scripts" on your database in order to create "DROP/CREATE CONSTRAINT and INDEXES" (use advanced button to tune wizard). Run portion of created SQL script to drop indexes and constraints.

2. Create one helper function and procedure as follows:

   CREATE FUNCTION [dbo].[GuidFromHash]
   (
       @Input nvarchar(MAX)
   )
   RETURNS nvarchar(MAX)
   AS
   BEGIN
       RETURN LOWER(SUBSTRING(@Input, 1,8)+'-'+SUBSTRING(@Input, 9,4)+'-'+SUBSTRING(@Input, 13,4)+'-'+SUBSTRING(@Input, 17,4)+'-'+SUBSTRING(@Input, 21,12))
   END
   
   CREATE PROCEDURE [dbo].[bigIntToGuid]
   (
       @table varchar(50),
       @column varchar(50)
   )
   AS
   DECLARE @SQL VARCHAR(MAX)
   
   SET @SQL='UPDATE @Table SET @Column=dbo.HashToGuid(''cc''+CONVERT(VARCHAR, HASHBYTES(''MD5'',LTRIM(@Column)),2))'
   SET @SQL=REPLACE(@SQL,'@Table',@Table)
   SET @SQL=REPLACE(@SQL,'@Column',@Column)
   EXEC(@SQL)
   
   SET @SQL='SELECT * FROM @Table'
   SET @SQL=REPLACE(@SQL,'@Table',@Table)
   SET @SQL=REPLACE(@SQL,'@Column',@Column)
   EXEC(@SQL)
   

3. Now comes the manual work for every table:

   1. Open table in designer (SQL Management Studio or other tool)
   2. Change bigint type of column to VARCHAR(50)
   3. Execute "EXEC bigIntToGuid 'myTable','myBigIntColumn'
   4. Back to table designer change column type to "uniqueidentifier"
   5. Optionally you can add default value: newid(), and/or set column as primary key
4. Open your sql generated script created in step 1
5. Select only portion of the script for constraint and index creation, and execute it

This approach ensures converting int to guid and keep data integrity.