GUID + Auto-Increment ID = Slow Performance?

Question

One of Jeff Atwood’s against using a GUID is

Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}') 

And I agree. I thought, now that the 16-byte identifier is no longer considered a huge undertaking, are the 16-byte + 4-byte IDs a practical compromise?

You can use clustered indexes and do most of your sequential (read: optimization) work with auto-increment identifiers. Combining, distributing, or other large enterprises will use the GUID as their primary workhorse.

So ... is anyone out there trying to mix the best of both worlds? What were the results of your enterprise? Of course, there is a problem when the PC (GUID) feeds on its entire index space next to another indexed field (auto-increment identifier), so I assume that the trade-off may be delicate and / or specific to a very narrow scenario.

Note: this question has addressed the problem before, but in terms of referential integrity management. I'm just wondering how I could combine a combination of PC / UK configurations on the table and their various effects on performance and scale. In fact, is it better to use a GUID as a PC and auto-increment as a non-unique index? Is it better to make them a unique key as a pair?

Thank you for your time.

Answer 1

Indexing int cheap and useful. Indexing a GUID is expensive (and protected from a rising edge, no one will be able to go through a GUID to query for individual rows from a table). SEQ GUID provide (relatively) quick index builds after many inserts, but eliminate most of the "randomness" that may provide some security for open access.

From this discussion in SQL Authority , a possible progression from int bigint GUID is rare and should not be taken into account until it takes time. To date, the safest and most affordable method for starting a new project is to use bigint PK AUTO_INCR with a GUID as an unindexed, unclassified field specifically designed to scald or merge shechmas throughout the life of the database. It occupies the top in terms of speed, but these effects will not be felt until you have enough lines to really take care of such things as the uniqueness of unique instances.

Answer 2

The problem with auto-increment identifiers is insert performance: if you insert thousands of rows, it means thousands of auto-increments at the database level. Inserting an index, on the other hand, is not a problem; hashing methods usually provide a good distribution.

You can use, for example, a 20-digit random number as your primary key and use this only: there is no need for a GUID!