Convert IDENTITY to uniqueidenitfier

Question

Convert IDENTITY to uniqueidenitfier

Wayne Berry

SSC Enthusiast

Points: 101
More actions
December 14, 2006 at 12:58 pm

#176049

Does anyone have a script of program to convert a int IDENTITY column to a uniqueidentifer? I am looking for something that also converts all the foriegn keys that point to the IDENTITY column, so it would be a multiple column convert. Like this maybe:
1) Create new uniqueidenitfier column with a temp name
2) Populate the new column with GUIDs
3) Create a new uniqueidenitfier column in all tables that have a foreign key to the column we are converting.
4) Populate all our relationial tables with the correct GUID from the primary table.
5) Drop all constraints
6) Delete the IDENTITY column
7) Rename the uniqueidenitfier column
8) Add the constraints back.
Thanks,
Wayne

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Monte Pix-294031 Old Hand Points: 371 More actions · Answer 1

That looks about the way I would do it. Just make sure you can re-name a uniqueidentifier column and reset it as a primary key before you start - I'm not sure about that part.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Personally, I would never advocate using a uniqueidentifier for your primary key. All of your indexes will be bigger and slower.

Nonetheless, if that is what your are doing, I suggest that you do one of the following:

Make the primary key unclustered so that every time a new record is inserted, the whole table does not have to be physically resorted.
Or specify the default constraint on the field as newsequentialid() instead of newid() ..... also to avoid resorting the table when inserting records.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Wayne Berry SSC Enthusiast Points: 101 More actions · Answer 3

My understanding is that I need uniqueidentifier as my primary keys if I am doing replication bi-directional and I have to get ride of the identities.

-Wayne

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

No, that is incorrect.

You need 2 separate things:

A primary key on every table
A rowguidecol (uniqueidentifier) column

Replication will add #2 if it does not already exist. Those should be 2 separate fields, not the same field.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Wayne Berry SSC Enthusiast Points: 101 More actions · Answer 5

Thanks Old Hand, I have been doing a lot of reading on this, can you post a link for some additional information where I can read up on this stradegy.

MohammedU SSCoach Points: 19644 More actions · Answer 6

If you are doing merge replication then it will add guid column but not in transactional replication...

I think you can do bi-directional transactional replication too...

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/mergperf.mspx

http://www.databasejournal.com/features/mssql/article.php/1438231

http://support.microsoft.com/kb/820675

http://msdn2.microsoft.com/en-us/library/ms151855.aspx

MohammedU
Microsoft SQL Server MVP