Vrftjkry

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:

1. 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.




2. 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.

If you're going to use a GUID key, simply use a clustered index with NEWSEQENTIALID() and move on with your application. The alternative designs are simply not predictably and significantly better when you consider the extra data structures and complexity they introduce. So while careful testing of your actual application might reveal a superior alternative design, just start with a simple clustered index on a sequentially-generated GUID.

For the gory details on why it doesn't really matter that the GUIDs are generated in the "middle" of the sort order, and the insertion point changes see: Good Page Splits and Sequential GUID Key Generation.

Using an INT (clustered) and a UNQUEIDENTIFIER (not) could save significant space if you have a lot of non-clustered indexes on the table, though be careful to make sure this internal value never leaks out into other layers of your application, and make sure it is well documented so others working on the DB know, as your distributed system could accidentally gain a global dependency on what should be a local only value.

There is a third option to consider: do you have anything else in the table that might benefit from being the clustering key? Perhaps a DATE column that sees a lot of range queries? Range queries on that column would then see a performance increase, you wouldn't be subject to the fragmentation see with arbitrary UUIDs and the date+uniquifier while still larger than an integer at 8 bytes is still smaller than the 16 needed for a UUID. Even if it is a DATETIME, or DATETIME2 depending on precision, the result is 12 bytes, so if you are using system managed history tables and this is a base table perhaps consider using sysStartTime.

Even if all you have is an INT (or smaller) that doesn't vary a lot, that plus the uniquifier will be a smaller key than a UUID and the uniquifier is effectively an INT IDENTITY(1,1) so it'll behave like your extra INT colummn without needing to pollute the data model with an extra column existing only for size/performance reasons.

(generated by the application, rather than SQL Servers built in NEWSEQUENTIALID() as it isn't truly sequential)

It is "sequential enough" to vastly reduce any fragmentation concern though, so don't go out of your way re-inventing the wheel if that is your only issue.