2

I have been wondering about the uniqueness of the GUID across the sql servers.

I have one central Database server and 100's of client databases (both SQL Servers). I have a merge replication (bi-directional) setup to sync the data between client and master servers. The sync process will happen 2-3 times a day.

For each of the tables to be synced I am using GUID as PrimaryKey and each table locally gets new records added and new GUIDs are generated locally.

When GUIDs are getting created at each client machine as well as at master DB server, how it will make sure it generates the unique GUID across all Client & Master DBs?

How it will keep track of GUID generated at other client/server DB, so that it will not repeat that GUID?

James Z
  • 12,209
  • 10
  • 24
  • 44
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • How are you generating the GUIDs? Are you using `newid()`, `newsequentialid()`, or some custom function? –  Sep 27 '13 at 10:15
  • 1
    possible duplicate of [Is a GUID unique 100% of the time?](http://stackoverflow.com/questions/39771/is-a-guid-unique-100-of-the-time) – Ben Sep 27 '13 at 10:19
  • Possible duplicate of [Guid Uniqueness On different machine](http://stackoverflow.com/questions/14074238/guid-uniqueness-on-different-machine) – John Y Jan 26 '16 at 02:31

4 Answers4

2

GUIDs are unique (for your purposes)

There are endless debates on the internet - I like this one

Community
  • 1
  • 1
SteveB
  • 1,474
  • 1
  • 13
  • 21
  • I know they are UNIQUE(though not 100%), but my question is how is it possible across servers? – AnandPhadke Sep 27 '13 at 10:18
  • Their uniqueness comes from extremely low probability of generating the same GUID. – Szymon Sep 27 '13 at 10:27
  • Then you need to researching how GUIDs are created - as @Ben has mentioned they are globally unique. The chances of a crash are extremely remote. – SteveB Sep 27 '13 at 10:41
  • Have a look through the link in my answer - there are some very good posts there covering everything you'd probably want to know about GUIDs. Enjoy :-) – SteveB Sep 27 '13 at 10:50
2

I think GUID's are not really necessarily unique. Their uniqueness comes from the fact that it's extremely unlikely to generate the same GUID randomly but that's all.

But for your purpose, that should be ok - they should be unique on a distributed system with extremely high probability.

Szymon
  • 42,577
  • 16
  • 96
  • 114
2

You will have to do more research, but I think GUID is based upon MAC address and timestamp, if I remember right.

http://www.sqlteam.com/article/uniqueidentifier-vs-identity

I know some MCM's who have come across a unique key violation on a GUID.

How can this happen? Well, in the Virtual World, you have virtual adapters.

If you copy one virtual machine from one host to another, you can have the same adapter, MAC address?

Now if both images are running at the same time, it is possible to get no unique GUIDs.

However, the condition is rare. You can always add another field to the key to make it unique.

There is a whole debate on whether or not to use a GUID as a clustered PK. Remember, any other index will take a copy of the PK in the leaf (nodes). This is 16 bytes for every record x number of indexes.

I hope this helps.

John

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
1

You don't need to do anything special to ensure a GUID/Uniqueidentifier is globally unique. That basic guarantee is the motivating requirement for the GUID.

Ben
  • 34,935
  • 6
  • 74
  • 113