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! )
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?