Using system time in reverse order to millisecond precision is probably good enough for most applications, if a little wasteful. Why use long if a smaller datatype (e.g. int) will do, especially if you may have hundreds of thousands, or even millions, of them - that's a lot of wasted bytes. Another alternative is to use the sequencing or identity generation capabilities of many RDBMS, though this is not without its problems (through Java).
In some of DB not sure about all, but specifically in ORACLE we have the concept of reverse indexing. this is the same as reverse indexing.
Joined: Aug 02, 2004
Of course in Adeel's example the "dates" would actually be stored as numbers, not strings (varchars) so any comparison wouldn't be character by character...
My only reason for suggesting reverse order (it doesn't make any difference for uniqueness) is that if you put the date in reverse order then you can compare and sort on that field to find earlier or later entries. In other words, the later the date the higher the number. That doesn't work if, for example you were to use DDMMYYYY format, e.g. 31012004 (Jan) is greater than 01022004 (Feb) but is an earlier date. It might be useful and there's no extra cost.