When is AutoNumber Random not really Random 2

[LittleSmudge]

I have a table which has an AutoNumber field that has it's increment type set to Random.

The database has been working well for a few months and the table is accumilating data.
I had reason to look at the raw data today and noticed that the supposidly Random AutoNumber field was very much NOT random.
The range of an Autonumber field is +/-2.147 E09
And I would expect to see a broad spread with 50% +ve and 50% -ve.
Imagine my surprise then to find 47 of the 50 records were all in the range 1052700000 to 1054700000

Furthermore - I investigated another unrelated database with a similar table in it and observed the same feature.


So I started to investigate.
Fortunately I also have a date/time field in the table that records the date and time that the record is created.

After some jiggery-pokery I worked out that the "Random Autonumber" is actually a calculation based on the date&time .

It is:-
MakeDT = Date/Time that record is created
SecInDay = Number of Seconds in a Day ( 86400 )
SecInHour = Number of Seconds in an hours ( 3600 )

RandAutoNum = ( ( MakeDT - #1/Jan/1970# ) * SecInDay ) - SecInHour



So if your Random AutoNumber field is not very random - you now know why.


'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[SgtJarrow]

Little Smudge is the man!!!! Thanks.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com