sql server - GUID PK + INT IDENTITY Clustered Index + Merge Replication + Foreign Keys -

i have database guid pks, , understand performance benefit adding int identity column (e.g. clusterid) , creating clustered index on it.

and joins there performance benefit using clusterid column foreign key. in parent/child situation child table have clustered index on parent clusterid.

however in merge replication scenario that's not possible because clusterid column not unique.

so i'm wondering how best gain performance benefits in situation.

for example:

tablea

• id guid (primary key)
• clusterid int identity (clustered index)`

tableb

• id guid (primary key)
• tableaid guid (foreign key tablea)
• tableaclusterid int (clustered index)`

i guess use trigger keep tableaclusterid up-to-date.

then query such as

select *  tableb b  b.tableaclusterid = @tableaclusterid 

would benefit increased performance.

is how done?

for heavy inserts guid perform better int identity because won't have contention on it. there technics fix it, still can problem. in other cases int/bigint perform better. unique in big range , have size 2-4 size smaller guid. if multiply these 12 bytes on number of rows using guids , multiply on number of non clustered indexes built on clustered guid index you'll see %% of database size used junk. assume, same percentage drop have in performance, if not more. size matters.