Ensure unique value

Question

Hello All,

I have a table with unique values within it and once a stored procedure is called, I use the following code within a sub-query to get a random value from the table:

SELECT TOP 1 UniqueID FROM UniqueValues
WHERE InitiatingID is NULL
ORDER BY NewID() ASC

I have however noticed that I am managing now and then (and I'm guessing two calls running simultaneously cause it) to retrieve the same unique value twice, which causes some issues within the program.

Is there any way (preferably not locking the table) to make the unique values ID generation completely unique - or unique enough to not affect two simultaneous calls? As a note, I need to keep the unique values and cannot use GUIDs directly here.

Thanks,

Kyle

---

Edit for clarification:

I am buffering the unique values. That's what the WHERE InitiatingID is NULL is all about. As a value gets picked out of the query, the InitiatingID is set and therefore cannot be used again until released. The problem is that in the milliseconds of that process setting the InitiatingID it seems that the value is getting picked up again, thus harming the process.

Answer 1

Random implies that you will get the same value twice randomly.

Why not using IDENTITY columns?

I wrote a blog post about manual ID generation some days ago here. Maybe that helps.

Greets Flo

Answer 2

What you're doing isn't really generating random unique values - which has a low probability of generating duplicates if you use the appropriate routines, but randomly selecting one item from a population - which, depending on the size of your population, will have a much higher chance of repeat occurrences. In fact, given enough repeated drawing, there will occasionally be repeats - if there weren't, it wouldn't be truly random.

If what you want is to never draw the same unique id in a row, you might consider buffering the 'old' unique id somewhere, and discarding your draw if it matches (or running a WHERE <> currentlydrawuniqueID).

Answer 3

What about using update with the output clause to select the UniqueId and set InitiatingId all at once. http://msdn.microsoft.com/en-US/library/ms177564(v=SQL.90).aspx

Something like: (Though I don't have SQL Server handy, so not tested.)

DECLARE @UniqueIDTable TABLE
(
    UniqueId int
)


UPDATE UniqueValues
SET InitiatingID = @InitiatingID
OUTPUT INSERTED.UniqueId into @UniqueIDTable
WHERE UniqueID = 
    (SELECT TOP 1 UniqueID FROM UniqueValues 
    WHERE InitiatingID is NULL 
    ORDER BY NewID() ASC)
AND InitiatingID is NULL