SQL Server unique GUID

Question

I understand in SQL Server GUIDs are MOSTLY unique, and also that the likelihood of a collision is remote, yet at the same time someone must win the lottery so I feel like it makes some sense to prepare for the possibility.

Which is faster/better practice

Using a technique where I assign a new GUID directly by just inserting a row and checking for an error (@@ERROR <> 0) and repeating until I don't get an error [which I suppose in theory would only at worst be once...]

or using an approach like this

DECLARE @MyGUID uniqueidentifier
SELECT @MyGUID = NewID()
if exists(select * from tablename where UserID=@MyGUID)

and looping over that till I find one not in use.

I like the 2nd approach because I can then have the GUID for use later on in the Stored Procedure so I'm currently leaning towards that one.

Answer 1

If you have at least one network adapter in your computer, then your GUIDs will be unique. IF you don't have, then the possibility of colliding with a guid generated on another machine exists in theory, but is never ever going to happen to you. Writing code to guard against duplicate guids is a total waste of time.

That being said, to enforce uniqueness of anything in a relational database is done by only one means: create a unique constraint on the data:

ALTER TABLE tablename ADD CONSTRAINT uniqueUSerID UNIQUE UserID;

Answer 2

The chances of a collision happening on your computer are much less likely than you winning the lottery.

Use NEWID() and don't worry about a hugely unlikely event.

If you declared the column as unique, it will not get persisted anyways.

Answer 3

To actually answer the question and not debate the merit of the question/perceived problem.

The first implementation will be the one you want to use for two reasons:

• Running a check exists before doing the insert for every single record your dealing with will in the end result in more resources being dedicated to something that is extremely unlikely to happen. (also the database will ensure the constraint on the column as well so if it does collide the data wont be committed)
• If you have an error on the first one, you can take a little extra time and handle the ERROR that is returned.

You can combine the two and declare the new uniqueidentifier insert into the table (if it works continue using it, else retry with a new one and then continue using it).

Generally you want to program for the most likely situation first and then handle the exceptions last.

Answer 4

The probability of a collision is so low - far less than that of winning the lottery - that it's more likely that cosmic rays will strike the RAM in the machine and cause your program to fail in some other, arbitrary manner. It's more likely that your error handling for this case will (now or eventually) contain an error that will lead to failure. Researchers who deliberately try to locate collisions using extensive computing power haven't succeeded so far in finding even one. I don't think it's worth your effort or time to handle this error case, at least until you've handled the wide range of far more likely hardware and software errors that may occur. I realize this is counterintuitive, but trust me.

Answer 5

Generally, I only use a GUID primary key if I'm dealing with distributed databases where clients need to add new records while offline. If this isn't your scenario, you're better off using an autoincremented int for your primary key.