Need to create unique value (not using identity or GUID)

[crystalized]

Hi All,

I am using SQLServer 2000 and am in a position where I need to create a unique numeric value for each record in a table. The table has a GUID as the key, but I need an additional unique value that is an 8 character numeric value.

My plan is to use a SP with something like the following structure
(psuedo-code)

Generate numeric value
If numeric value is unique
      insert numeric value
Else
      Change some element of hash function
      Regenerate numeric value
      If numeric Value is unique
            Insert numeric value
      Else 
            Return Error message

Now I know that if the key value was an identity field I could take the maximum value and do some kind of hash function to create a new value that would be unique. My problem here is that I have a GUID to work from and I am not sure what kind of "hash" function I should use to create that numeric value.

As you see I have already accomodated in my plan for a failure to create a unique value on the first try but I really do not want to make many more than 2 trials, but even more so I do not want to return that error message unless absolutely necessary.

If anyone has any ideas on this it would be very helpful - even if they are just some starting points.

Thanks in Advance Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[rac2]

Why do all that sophisticated hashing? Would a simple sequence work? Start with '10000000'. For the next one, retrieve the MAX() value, convert to INT, add 1, and convert back to CHAR(8).

 

[crystalized]

rac2,

It has to be random, they can not be sequential, if they could be sequential I would just use an identity field. Oh how I wish life were that simple LOL

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[tlbroadbent]

Perhaps you can use a script like the following. Make sure you have an index on the Unique ID Column or this process will crawl as the table grows.

-- Declare variables for process
Declare @id int, @cid char(8)
Declare @success int

-- initialize success flag
Set @success=0

-- Loop until insert is successful
While @success = 0
Begin
-- Generate random number
Set @id=RAND((DATEPART(mm, GETDATE()) * 100000)
+ (DATEPART(ss, GETDATE()) * 1000)
+ DATEPART(ms, GETDATE())) * 99999999 +1

-- Convert number to string with leading zeros
Set @cid=replace(str(@id,8),' ','0')

-- Check if value does not already exist in table
If Not Exists (Select * From #tmp Where id=@cid)
Begin
-- If value not in table, insert row
Insert #tmp Values(@cid)

-- Set success flag to end loop
Set @success=1
End
End Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[crystalized]

Terry,

Thank you very much, especially for the reminder to index the column (I would have forgotten that, as I have not used indexes very extensively

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[crystalized]

As I was working with this process I discovered something about the rand function that I was not aware of. Given the same seed value you will always get the same value from the Rand function. This reared its head quite badly when I was just using the datepart(ss, getdate) but even happened when I added the datepart(ms,getdate) portion to the seed value.

I had thought that it would return a different value each time called (in a random fashion) even if given the same seed value, this is not the case. When you run it without the seed value the system will generate the seed value for you which gives me the type of result I expected.

I had scripted something to run the sp I built (had it execute 20 times) and I was consistently getting results that caused the while loop to execute multiple times (from seperate executions of the sp). I guess that is a good indicator of how quickly it can execute an sp ;-)

I did eventually get the results I was looking for, but I figured I would just post this as a word of caution for anyone who might misunderstand the rand function like I did.



Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[tlbroadbent]

I am quite aware of the limitation of RAND. Note the formula I posted for the RAND function seed. However, here is a slightly modified script that may work better.

-- Declare variables for process
Declare @id int, @cid char(8)
Declare @success int

-- Seed the random number generator
Set @id=RAND((DATEPART(mm, GETDATE()) * 100000)
+ (DATEPART(ss, GETDATE()) * 1000)
+ DATEPART(ms, GETDATE()))

-- initialize success flag
Set @success=0

-- Loop until insert is successful
While @success = 0
Begin
-- Generate random number
Set @id=RAND() * 99999999 +1

-- Convert number to string with leading zeros
Set @cid=replace(str(@id,8),' ','0')

-- Check if value does not already exist in table
If Not Exists (Select * From #tmp Where id=@cid)
Begin
-- If value not in table, insert row
Insert #tmp Values(@cid)

-- Set success flag to end loop
Set @success=1
End
End Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[crystalized]

Terry,

I figured you would be fully aware of that limitation, I figured I would just note it for anyone who might be searching and find this thread.

And thanks for additional information!

Crystal Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde