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;