Via this link, I understand that the GUID isn't good like a clustered index, but it may be distinctively produced anywhere. It's needed for many advanced SQL Server features like replication, etc.

Could it be considered bad design if I wish to possess a GUID column like a typical Primary Key ? Also this assumes another int identity column for my clustering ID, so that as an additional benefit a "easy to useInch id?


After viewing your feedback, I understand I did not really word my question right. I realize that the Guid constitutes a good (even when its overkill) PK, but a poor clustering index (generally). My question more directly requested, is, could it be bad to include another "int identity" column to do something because the clustering index?

I believed the Guid will be the PK and employ it to construct all associations/joins etc. I Quickly would rather than utilizing a natural key for that Cluster Index, I'd add one more "ID" that does not data-specific. What I am wondering is the fact that bad?

If you are planning to produce the identity area anyway, use that because the primary key. Consider querying this data. Ints are faster for joins and far simpler to specify when writing queries.

Make use of the GUID should you must for replication, try not to utilize it like a primary key.

Utilizing a GUID is lazy -- i.e., the DBA can not be bothered to model his data correctly. Plus it offers very bad join performance -- typically (16-byte type with poor locality).

Could it be a poor design, if I wish to possess a GUID column as my typical Primary Key, along with a separate, int identity column for my clustering ID, so that as an additional benefit a "easy to useInch id?

Yes it's very bad -- first of all you wouldn't want several "artificial" candidate key for the table. Next, if you prefer a easy to use id for secrets only use a set length type for example char[8] or binary(8) -- ideally binary because the sort will not make use of the locale you could utilize 16-byte types however you will observe a degeneration in performance -- however less bad as GUID's. You should use these fixed types to construct your personal user-friendly allocation plan that preserves some locality but creates sensible and significant id's.

For example:

If you're writing some kind of a CRM system (allows say online quotes) and also you want an very easy to use type for instance a insurance quote reference (QR) that appears like so "AD Vehicle MT 122299432".

Within this situation -- because the quote length huge -- I'd produce a separate LUT/Symboltable to solve the quote mention of actual identifier used. however i will divorce this LUT in the relaxation from the model, I'll never make use of the quote reference elsewhere within the model, especially not within the table representing the QR's.

Create Table QRLut
    bigint bigint_id;
    char(32) QR;

If my model has one table that signifies the QR and 20 other tables featuring the bigint QR like a foreign key -- the truth that a bigint can be used allows my DB to scale well- the wider the join predicates the greater contention is triggered around the memory bus -- and the quantity of contention around the memory bus determines how good your CPU's could be saturated (multiple CPU's).

You may think with this particular example you could just put the user-friendly QR within the table that really signifies the quote, however bear in mind that SQL server gathers statistics on tables and indices, and you won't want to allow the server make caching choices in line with the user-friendly QR -- as it is huge and wastefull.

What exactly are you planning to complete using the GUID? The int identity column may also be unique within that table. Would you really need or be prepared to need a chance to replicate? If that's the case, is applying a GUID really more suitable inside your architecture over handling identity posts through among the identity range mangement options?

If you want the "pretty" ids produced while using Active Record pattern, i quickly think I'd use it rather than GUIDs. Should you choose need replication, then use among the replication methods right for identity posts.