CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AnandPhadke
AnandPhadke

Reputation: 13486

Is GUID unique across SQL servers?

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?

Upvotes: 3

Views: 3433

Answers (4)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

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

Upvotes: 2

Ben
Ben

Reputation: 35613

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.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

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.

Upvotes: 2

SteveB
SteveB

Reputation: 1514

GUIDs are unique (for your purposes)

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

Upvotes: 2

Related Questions