Best way to check if exists and optimization

Transact-SQL
1

Hey,

What is the best way to find if exists and if exist generate new uuid. can you plz help me with optimizing below query

declare @uuid uniqueidentifier =newid()
if exists(select uuid from temp where uuid in (@uuid))
set @uuid=newid()
select @uuid as UUID

2

It is almost impossible for a newid() to already exist ... so it should be enough to allocate a newid and then if you find that it DOES exist just loop round and allocate another one.

We don't bother. We have a unique index on the column so IF we were to try to add a GUID that already existed then the APP would fail with an error. It is so unlikely to ever happen that we are happy to terminate the program with a critical error in that situation.

3

Use a WHILE loop instead of an IF:

declare @uuid uniqueidentifier =newid()
WHILE exists(select uuid from temp where uuid in (@uuid))
    set @uuid=newid();
select @uuid as UUID
4

That still leaves a risk that the GUID exists by the time any INSERT into [temp] is performed subsequently ...

5

FWIW, the description in BOL is "Creates a unique value of type uniqueidentifier." If that can be relied upon, the issue resolves itself.

6

I don't think that it can. Definitely possible to get a duplicate. Statistically impossible though. On the same machine then supposedly impossible because algorithm includes time (although I am not sure that is the case for new formulae). But, assuming time included, what about for the hour when the clocks go back in the Fall? Or some hardware issue with the clock ... or it being reset a smidgeon by SYNC with Time Server ...

GUIDs generated by TWO machines then definitely (albeit rate) evidence of duplicates.

On computers with poor implementations of GUID algorithm ... chances increase

Like I said, it is not something I worry about, but we do put a Unique Index on GUID columns just-in-case. If we have a GUID column we will likely be retrieving records based on it, so the Index will come in handy anyway, and the SQL Query Planner will appreciate the Unique attribute :smile:

7

That's an extraordinarily contradictory mix of quotes. First you say it's so rare you don't even bother to check at all ahead of time for a collision. Then you say it's too "risky" to check only once ahead of time if the actual insert will take a few seconds to occur! Huh??

8

We are relying on our Unique Index catching any error. We don't think it will ever happen, but we are happy that SQL terminates our action with an error if it does.

The O/P is generating a GUID and then testing that it does not exist in the database, and is then going to use it to INSERT - by which time [I am suggesting] some other user may have inserted a duplicate. The O/P is going to the trouble of checking that the GUID generated by NewID() does not exist, so I have presumed that avoiding DUPs is of critical importance.

I think they are two different requirements.

9

Thanks for your reply guys..

Yes i know GUID is unique and there are less chances that it may occur again.

Yes i will use while loop to check..

Thanks again