> This brings up an interesting question I've had for a while: I've seen a few
> (but only a few!) developers use CreateUUID() for creating a record's PKID,
> and therefore have the PKIDs be strings rather than integers.
Storage and internal representation is not necessarily related to
representation in the front-end. A UUID (or GUID) could easily be
stored as an 128 bit integer. [1]
> record was actually inserted into the DB) I questioned them about the
> impact this has on their joins and a few said they never saw a real decrease
> in performance (I don't have any proof, but I inherently distrust that
> statement!)
It shouldn't really matter much if the database uses a 128 bit
integer approach. A bit more if it uses a string, but unless you
use very large (and narrow) tables and indexes I expect it to
drown in the noise if you benchmark it.
> and a few others say that they used that only for creating a
> unique ID, but that they had a separate ID field in each table that was
> numeric which they used for joins. This part seemed like overkill to me -
> surely there's some stored procedure that accomplishes all of this?
Don't rely on stored procedures to fix design errors. Using any
field other than the primary key for making foreign keys is not
smart.
> I thought the whole point of "autonumber" fields was exactly
> to make this issue moot.
There are situations where a simple autonumber doesn't do.
Replication and inheritance for instance.
[1] In fact, I would argue that is the only right way to store
them, since there are 2 competing formats [2] with different
generation algorithms and a different length string
representation. Having a database that accepts both
representations in the input and stores them as a 128 bit integer
would be a nice way around that. You should be able to verify in
your dbms manual what the internal representation of a UUID is
and which type is used.
[2] Microsoft uses a different format as CF for instance. But
both formats are guaranteed not to overlap eachother ever in a
128 bit representation.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
