I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-service architecture. There seems to be a lot of debate on what's actually best.
The two main solutions appear to be:
Have the primary key as a sequential GUID (generated by the application, rather than SQL Servers built in NEWSEQUENTIALID() as it isn't truly sequential) with a clustered index.
Have two primary keys, a randomly generated GUID with a non-clustered index and a basic int IDENTITY column with a clustered index -- the approach is described best in this stackoverflow response here
I'm not sure which solution is best. The first method seems to be the most popular and widely used from what I've read. The second seems to have this benefit from that linked stackoverflow response:
Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible.
Any advice would be appreciated.