0

How do I set the nId column (from the example table creation code below) up as uniqueidentifier to prevent the merge publication wizard from creating an unneeded GUID column?

I am trying to set up merge publication in SQL Server Management Studio 2019.

My published tables use int and bigint identity for their primary key.

Here is an example creation script for one of my tables:

create table MySchema.MyTable 
(
    nId bigint identity(1,1) primary key,
    cName varchar(100)
);

However, when I use the "new publication" wizard, I come to this step where it says it will automatically create GUID column for published tables:

publication wizard warning

I do not think I should need this extra column, as merge publication assigns identity ranges for each publisher/subscriber, so there should never be a reason the nId can't serve as the uniqueidentifier, but I can't seem to figure out how to set the identity column as also the uniqueidentifier.

Ken White
  • 123,280
  • 14
  • 225
  • 444
ruttergod
  • 167
  • 9
  • 1
    `UNIQUEIDENTIFIER` is a type. A `BIGINT` does not qualify. Per the message, merge replication works through a `ROWGUIDCOL` column, always; the fact that you're also using ranges on your identity value is immaterial, as the engine needs a column that's guaranteed to be immutable (and while a PK is seldom updated, this is still permitted). You can create that column yourself or have the wizard do it, but you can't omit it. – Jeroen Mostert Mar 25 '22 at 16:35
  • Thanks! Are there any performance considerations in whether I should just let the wizard add that column or if I should add it myself? Like if the wizard adds it, does it automatically cluster based on the new guid column or will it still cluster on the ```bigint``` column? – ruttergod Mar 25 '22 at 16:53
  • 2
    The [docs](https://learn.microsoft.com/sql/relational-databases/replication/administration/enhance-merge-replication-performance#snapshot-considerations) cover this; it's recommended to create it in advance. Clustering on it is probably not a good idea (the identity is still better for that by virtue of being smaller and presumably being used in actual business queries); the only requirement for the row GUID column is that it has a unique index, but that needn't be clustered. – Jeroen Mostert Mar 25 '22 at 16:56

0 Answers0