1

When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index... So, will it slow down my queries or not ?

I am explaining some description of my problem.. I have two publisher which is replicating data into one subscriber(i.e primary Alwayson) then how to maintain primary key value in this scenario ?

So,Please give me a idea to maintain primary key value ?

3

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would if you were using an identity or sequential dates).

You can somewhat reduce this by using NEWSEQUENTIALID instead of NEWID (assuming the GUID is generated by the database rather than your application).

3

If there is any possibilty of avoiding GUID as Primary Key / Unique Clustered Index, then take this option!

A good (surrogate-) key should be:

  • Narrow (NO)
  • Unique (GUID is)
  • Static (GUID is)
  • Ever Increasing (NO)

GUID (uniqueidentifier) needs 16 bytes - int (numbers up to 2^31 - 1) needs 4 bytes, bigint (numbers up ot 2^63 -1 which is far more than you will ever need) needs 8 bytes!

If there are no naturaly key candidates i always use a surrogate key of int/bigint with identity specification. I never had the case where i had to use GUID.

  • I don't disagree, but there are a couple of caveats worth stating: 1. sometimes a UUID is the best tool for the job (where IDs from distributed systems need a good guarantee of uniqueness, for instance) (or at least a good tool for the job, there may be other options) so there are cases where it can't be avoided; 2. some DBs offer "sequential UUIDs" which if implemented well sufficiently constitute "ever increasing" without losing the advantages of a UUID. – David Spillett Jul 25 '16 at 13:49
  • That's right, there are cases where there are no other (at least no easier) options. Sequential UUIDs (NEWSEQUENTIALID as mentioned by Cody Konior in his answer) may be ever increasing, but i think it's not possible to see at first sight, which record in a table is "newer". That's the great benefit of using easy readable and comparable surrogate keys. Would be interesting if there are any differences in performance... – CeOnSql Jul 26 '16 at 9:12
  • You should be wary of ascribing any meaning to the increment of a surrogate key, you risk growing functional dependencies where they should not exist, changing the surrogate key into a badly defined natural key. A surrogate key should not carry nor be assumed to carry any meaning beyond being a key value to identify a row. If the age or records is important, hold a DateCreated column or similar. – David Spillett Jul 26 '16 at 11:25
  • in a world where every database is perfectly designed - yes! in real-world, where you have to deal with database designs from different persons, different personal preferences and third party software companies: well... – CeOnSql Jul 27 '16 at 10:25
2

It depends. Using a normal UUID as your clustering key will result in greater fragmentation in the table and so waste disk space and memory. You can mitigate this mostly with NEWSEQUENTIALID if that fits your use of the table, or of course you could use some other key as the clustering key (specify NONCLUSTERED when defining the primary key and create another index as CLUSTERED) which you may want anyway for efficiency elsewhere.

Not the answer you're looking for? Browse other questions tagged or ask your own question.