July 19, 2011 at 9:10 am
July 19, 2011 at 9:16 am
July 19, 2011 at 9:17 am
Jules Bonnot (7/19/2011)
Does anyone how unique this is? How many times I would have to run it before I found a duplicate.
select ABS(CAST(CAST((newid()) AS VARBINARY(5)) AS Bigint))
Create a table with PK on it.
Run inserts into that table untill it fails.
I'm pretty sure it'll fail but it "should" be a rare occurance.
July 24, 2011 at 12:49 pm
What exactly are you trying to achieve?
Are you attempting to come up with some sort of key generator?
Whereas a uniqueidentifier is 16 bytes a bigint is 8 but random keys are still going to cause fragmentation issues.
July 24, 2011 at 2:55 pm
Go here and calculate the probability of it happening
July 24, 2011 at 3:10 pm
Jules Bonnot (7/19/2011)
Does anyone how unique this is? How many times I would have to run it before I found a duplicate.
select ABS(CAST(CAST((newid()) AS VARBINARY(5)) AS Bigint))
I did the analysis, once. First, it's a pseudorandom number and it won't repeat on the same machine until all of the numbers have been used.
To give you an idea of how big the number really is, if one "unit" of the number equalled 1 statute mile, there would be enough numbers to stretch across 17 quadrillion Milky Way Galaxies.
That, notwithstanding, there is no guarantee that the numbers generated from 2 or more machines are unique... and least not anymore because they changed from Type 1 GUID's (partially based on the Mac Address) to Type 4 GUID's which are nothing more than random numbers with a couple of constant columns. A friend of mine from UPS tells me the tale of when two machines started generating more than the chance intersection of two such random numbers... it made a real mess.
Bottom line? Even though the chances of two machines generating the same number is incredibly remote, make sure you have a UNIQUE constraint on columns that contain GUID information because they not guaranteed to be unique.
--Jeff Moden
July 25, 2011 at 2:14 pm
If your intent is to generate unique integer values for use as a surrogate keys, then I'd reccomend IDENTITY over some function that returns a random number. An identity value will be sequential, which is what you want if the key is also clustered. If there are multiple tables or federated servers involved, and you don't want key values repeated across, then you can solve that by defining seed values and check constraints on each.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2011 at 4:01 pm
If you accept the premise that your surrogate keys are going to be BIGINT values then you could try the following
The right most 6 characters are just a sequential number
The 7th character is a key generator number (so you can have a max of10 key generators)
The left most characters are milliseconds since 1st July 2011.
Providing no one key generator wants to push in more than 1 million records per millisecond that should be good for 40 years.
July 25, 2011 at 5:33 pm
David.Poole (7/25/2011)
If you accept the premise that your surrogate keys are going to be BIGINT values then you could try the followingThe right most 6 characters are just a sequential number
The 7th character is a key generator number (so you can have a max of10 key generators)
The left most characters are milliseconds since 1st July 2011.
Providing no one key generator wants to push in more than 1 million records per millisecond that should be good for 40 years.
Interesting idea. That seems like a nice way to create some unique numbers, though I slightly worry about performance here. Curious how this might perform across a busy system.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply