Sql-server – Primary key and index column for Merge replication- Id vs GUID

indexreplicationschemasql serveruuid

I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the future) used to synchronize the databases and tables and it uses the UUID column for it.
I am having a few issues with performance.

Update: The UUID is supplied from the application end – not generated during data load.

Based on the research I have done (here is where I got much clarity on the issue Thank you! Thank you! )
https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega][1]

Here is the takeaway-

  • UUID as PK is ok- as long as it is is NOT Clustered
  • Do not create a clustered index on columns if you are not going to
    search by the columns
  • Identity (bigint) + HostName much efficient as a clustered index

I am currently planning on designing to include an Identity , DatabaseServerName and Date as a clustered Index and the UUID as a primary key only.

Here are my questions:-

  • since I DO WANT to enforce FK using the UUID columns – do I still
    create a separate clustered index on identity?
  • All the joins are going to be based on the UUID- will I still benefit
    from the clustered index?
  • Should I rethink joins on the UUID?If so- how should I go about it?

Best Answer

I disagree with some of that advice. GUID as a clustered PK is ok as long as you use sequential GUID generation, eg NEWSEQUENTIALID().

Sequential GUID generation, even if only "locally" sequential, mitigates the bulk of the performance problems you will experience with random GUID generation. The alternative designs end up just needing too many indexes.

You need GUIDs for distributed key generation, and that's never going to be as efficient as using int, but the uniqueidentifier column is a requirement for Merge Replication. So your tables can simply be

create table SomeTable(Id uniqueidentifier rowguidcol default newsequentialid(), . . .)
Related Question