Post reply

Convert IDENTITY to uniqueidenitfier

  • 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

     

     

  • December 15, 2006 at 6:48 am

    #678313

    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.

  • December 16, 2006 at 5:53 pm

    #678536

    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:

    1. Make the primary key unclustered so that every time a new record is inserted, the whole table does not have to be physically resorted.
    2. 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]

  • December 18, 2006 at 8:49 pm

    #678850

    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

  • December 18, 2006 at 9:10 pm

    #678851

    No, that is incorrect.

    You need 2 separate things:

    1. A primary key on every table
    2. 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]

  • December 18, 2006 at 9:17 pm

    #678852

    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.

  • December 19, 2006 at 1:18 am

    #678862

    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

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

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