Hello SQL Gurus
I am new to Database design. We are building an application that is expected to have mid-level volumes - Around 10K to 100K records per day based on the day of the week. It is a retail application and will have multiple databases with a separate database for each branch and one for the central or Head Office.
We will be using SQL Merge replication to merge data between the branches and the head office.
I am thinking of having a Primary Key that uses two Int columns - one of which will be the branch ID and another will be a simple sequence. I am also planning to have this as the clustered index. Will this design work or will I need to use a GUID as a primary key with the above key as a separate clustered index considering that we will be using SQL Merge Replication. What would be the best primary index for the tables?
We will be on SQL Server 2016 SP2.
Any guidance on this will be highly appreciated.
Thank you all in advance.