locked
primary key on rowguid column in merge replication RRS feed

  • Question

  • Hi ALL,

                     Can i create primary key on ROWGUID column in merge repicated tables...if i created PK are thy would be any issues with ongoing merge replication....

    Tuesday, July 27, 2010 2:13 AM

Answers

  • yes you can. You will need to make it a rowguidcol, ie

     

    alter table mytable alter column pk uniqueidentifier not null
    GO
    alter table mytable alter column pk  add ROWGUIDCOL
    GO
    alter table mytable add constraint mytablePK primary key  (pk)


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, July 27, 2010 1:34 PM
    Answerer
  • Hi kashif412,

     

    According to Books Online(BOL), when the Snapshot Agent for a Merge Replication first time runs, a column named rowguid is added to each published table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used).

     

    Merge replication requires that each published table have a ROWGUIDCOL column. If a ROWGUIDCOL column does not exist in the table before the Snapshot Agent creates the initial snapshot files, the agent must first add and populate the ROWGUIDCOL column. To gain a performance advantage when generating snapshots during merge replication, create the ROWGUIDCOL column on each table before publishing. The column can have any name (rowguid is used by the Snapshot Agent by default), but must have the following data type characteristics:

    ·         A data type of UNIQUEIDENTIFIER.

    ·         A default of NEWSEQUENTIALID() or NEWID(). NEWSEQUENTIALID() is recommended because it can provide increased performance when making and tracking changes.

    ·         The ROWGUIDCOL property set.

    ·         A unique index on the column.

     

    In this case, of course we can create primary key on the column of data type uniqueidentifier with the ROWGUIDCOL property set in merge replicated table.

     

    And based on my test, whether drop primary key on the column or not, we also can delete the publication.

     

    For more information, please visit the following links:

    How Merge Replication Initializes Publications and Subscriptions: http://msdn.microsoft.com/en-us/library/ms151769.aspx

    Enhancing Merge Replication Performance: http://msdn.microsoft.com/en-us/library/ms152770.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    Wednesday, July 28, 2010 8:02 AM

All replies

  • kashif412,

    I'm not sure about this but I don't think that sql server will allow you to change a merge rowguid column to a PK, from the GUI or from TSQL.

    you can add another uniqueidentifier column and make it as a PK.

    Tuesday, July 27, 2010 1:07 PM
  • yes you can. You will need to make it a rowguidcol, ie

     

    alter table mytable alter column pk uniqueidentifier not null
    GO
    alter table mytable alter column pk  add ROWGUIDCOL
    GO
    alter table mytable add constraint mytablePK primary key  (pk)


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, July 27, 2010 1:34 PM
    Answerer
  • Thanks Hilary  and Wissam for your reply....after creating PK on rowguid column ...would be any issues with ongoing replication after creating Primary key ????......i see like cant delete publication till we drop primary key on column....plz clarify this...

     

    Thanks a lot

    Tuesday, July 27, 2010 5:20 PM
  • Hi kashif412,

     

    According to Books Online(BOL), when the Snapshot Agent for a Merge Replication first time runs, a column named rowguid is added to each published table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used).

     

    Merge replication requires that each published table have a ROWGUIDCOL column. If a ROWGUIDCOL column does not exist in the table before the Snapshot Agent creates the initial snapshot files, the agent must first add and populate the ROWGUIDCOL column. To gain a performance advantage when generating snapshots during merge replication, create the ROWGUIDCOL column on each table before publishing. The column can have any name (rowguid is used by the Snapshot Agent by default), but must have the following data type characteristics:

    ·         A data type of UNIQUEIDENTIFIER.

    ·         A default of NEWSEQUENTIALID() or NEWID(). NEWSEQUENTIALID() is recommended because it can provide increased performance when making and tracking changes.

    ·         The ROWGUIDCOL property set.

    ·         A unique index on the column.

     

    In this case, of course we can create primary key on the column of data type uniqueidentifier with the ROWGUIDCOL property set in merge replicated table.

     

    And based on my test, whether drop primary key on the column or not, we also can delete the publication.

     

    For more information, please visit the following links:

    How Merge Replication Initializes Publications and Subscriptions: http://msdn.microsoft.com/en-us/library/ms151769.aspx

    Enhancing Merge Replication Performance: http://msdn.microsoft.com/en-us/library/ms152770.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    Wednesday, July 28, 2010 8:02 AM
  • hello i dont want to add rowguid column to table ,

    because i already  have unique identifier with primary key. 


    Rahul Hundare

    Wednesday, August 28, 2013 12:18 PM